Microsoft Excel - Sorting multiple values. - Asked By Sir John Money on 11-Aug-11 02:37 PM

Product Product Code Desc UOM Eaches per UOM Posting period
002342341 HAND FX SURGICAL TECHNIQUE EA 1 1
002342344 ALPS LARGE FRAG PLATING SYS ST EA 1 1
002342345 ALPS SMALL FRAG ST EA 1 1
002342346 ALPS DISTAL TIBIA ST EA 1 1
002342354 ALPS Large Frag Plating Ger EA 1 1

159963046 DUR OPT CERAMIC 46X28 EA 1 1
159963046 DUR OPT CERAMIC 46X28 EA 1 7
159963048 DUR OPT CERAMIC 48 OR 50X28 EA 1 1
159963048 DUR OPT CERAMIC 48 OR 50X28 EA 1 7

I would greatly appreciate your assistance in figuring this out. As you can see in the product column,some of the products numbers are the same (last 4). However, the posting period for these products are different. (e.g.: the last four product numbers are in similar pairs. However, their corresponding posting period are either 1 (january) or 7 (July). For the preceeding 5  products, each has a unique posting code of 1 (January).

What I need to do is to figure out a way to sort/query or use some other approach to efficiently pick out the rows  with multiple posting date, and delete the products with the 1 (january) posting date. Keep the 7 (July) record. 

I have very large databases to go thru and I am looking for a function or code that do this for me. My primary challenge is that the singler product's posting dates are also 1 (January) and I need/want to keep those. Only the 1 (January) posting period with corresponding Product which also has a 7  (July Posting Period), I want to delete.

Thank YOu very much in advance
M.B
wally eye replied to Sir John Money on 11-Aug-11 07:22 PM
Assuming your Product code starts in A2 and Posting period is in E2:

F2: =if(a2=a1,"Delete","")

This assumes you have the data sorted by product code and then posting period.

Auto fill the formula to the bottom of your sheet, filter column F for the word Delete, Select all the rows, then GoToSpecial Visible Cells and delete them...

This will work fine for a one-off, you could use a macro (VBA) if you are doing this on a regular basis.
Sir John Money replied to wally eye on 11-Aug-11 09:30 PM
Thank you Wally Eye for the response. I will try and let you know how it goes.

Thanks again.
Pichart Y. replied to Sir John Money on 11-Aug-11 09:34 PM
Hi Sir John Money,

Suppose you have only 9 records, in F2 input this formula =COUNTIF(A2:$A$10,A2)
The results more than 1 are the previous duplicate ones, filter and delete them.


pichart Y
Sir John Money replied to wally eye on 11-Aug-11 10:56 PM
Wally: Thanks again for your response. Unfortunately, as I attempt to apply the code, I realize that my problem still exists. I think that this code would work for products with unique post period dates. However, in my situation, I have two post period values: 1 (January) and 7 (July). But 3 product values( two of which are identical with one having the same posting period value (1 [January]) (see above spread sheet sample). Additional Illustration of the scenario: Product Codes: Posting Period: P 1 PP 1 PP 7 I need to pick out the Product Code PP, which also has a Posting Period 1 out of the data. Using formula you provided, both Products: (P & PP with Posting Periods: 1 & 1) respectively would be deleted. However, I only need to delete PP with with Posting Period 1. I hope I articulate my need clearly for you or anyone else to easily understand.
Sir John Money replied to Pichart Y. on 11-Aug-11 11:13 PM
Pichart Y.

Thank you. I am going to try it now. Also, please see my follow up reply to Wally, see if anything I add changes your guidance.

Thanks again.
Radhika roy replied to Sir John Money on 12-Aug-11 10:52 AM

 

 Follow these steps to sort multiple values-

  • Select one cell in the column you want to sort.

  • Press Ctrl + A, to select the entire region.
  • Check the selected area, to make sure that all the data is included. For example, in the screen shot below, hidden column E is blank, so columns at the left are not selected.
  • sort hidden column

  • If all the data was not selected, fix any blank columns or rows, and try again. Or, use the Sort Dialog box, as described in the next section.
  • If all the data is selected, click Sort A to Z (smallest to largest) or Sort Z to A (largest to smallest)
  • Before you do anything else, check the data, to ensure that the rows have sorted correctly. If things look wrong, click the Undo button on the toolbar

  • Hope this will help you.

    Sir John Money replied to Radhika roy on 12-Aug-11 01:01 PM
    Radhica,

    Thank you for the response. However, my sorting need is not a straight forward type. I need to sort 1 colum with 3 values (2 of the 3 are identical) and row over to another colum where (another 2 of the 3 are identical) except that the the two that are identical on the first column aren't the same two which are identical on the second column.

    for instance:

    Column 1:        Column 2:
    A                  1
    B                  1    
    B                  2

    I need to identified (sort) all the (see below) and delete them from spread sheet. Keep the A    1s & B   2s

    Column 1:        Column 2:
    B                  1


    As you can see, in column 1, the Bs are similar value. However, in column 2, one of the B has similar value as the A. And that's the B that I need out.
    Pichart Y. replied to Sir John Money on 13-Aug-11 09:19 PM
    How about this one, is it Ok for your question?

    pichart Y.
    Sir John Money replied to Pichart Y. on 16-Aug-11 12:57 PM
    Hello Pichart, Which one? There is no additional info in your post. Please resubmit with intended info. Thanks.
    Pichart Y. replied to Sir John Money on 16-Aug-11 11:49 PM

    Hi Sir John money,

    Actually I want to check something about my post on 11Aug2011,there I use the formula, any way, let's try with this new one. Today I use macro. here is the attachment to try --->FindDup&delete_macro.zip

    ------------------- code start ----------------------
    Sub CleanIncompleteRow()

    'Create Index to control row number for original sequence
    Range("F1").Value = "Control Index"
    For i = 2 To Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
    If Range("F" & i - 1).Value = "Control Index" Then
    Range("F" & i).Value = 1
    Else
    Range("F" & i).Value = Range("F" & i - 1).Value + 1
    End If
    Next i

    'Sort to get the lastest Posting period, make sure the last post will be kept
    lstRow = Range("B" & Rows.Count).End(xlUp).Row
     Range("A1:F" & lstRow).Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E2:E" & lstRow) _
          , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
          .SetRange Range("A1:F" & lstRow)
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
        End With

    'Find the row those are duplicate ,then delete all except the most new records
    Sheets(1).Range("A:A").EntireColumn.Insert
    Range("A1").Value = "Check"
    Range("A2:A" & Sheets(1).Range("B" & Rows.Count).End(xlUp).Row).Select
    Selection.FormulaR1C1 = "=IF(COUNTIF(RC[1]:R10C2,RC[1])>1,NA())"
    Selection.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Select
    Selection.Delete
    Range("A:A").Delete

    'Sort to get the the original sequence
    lstRow = Range("B" & Rows.Count).End(xlUp).Row
     Range("A1:F" & lstRow).Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F2:F" & lstRow) _
          , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
          .SetRange Range("A1:F" & lstRow)
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
        End With
    Range("F:F").Delete

    Range("A2").Select
    End Sub

    ----------------------- End Code --------------------------

    Pichart Y.
    Sir John Money replied to Pichart Y. on 17-Aug-11 10:43 AM
    Good morning Pichart, Thank you for your assistance. Is this a vb code? Should I just copy and paste into a micros/VB editor? If you can attach a sample spreadsheet, I would be grateful. Thanks
    Pichart Y. replied to Sir John Money on 17-Aug-11 11:15 AM
    Hi John Money,

    Where are you there, it is morning but for me it is 10:15 pm. almost midnight, time in Thailand?

    I have attached the sample file in my last post...click at blue letter in my last post.

    Pichart Y.
    Sir John Money replied to Pichart Y. on 17-Aug-11 11:51 AM
    Pichart, Thank you once again. I will look it up. I am in the U.S. Sorry for the confusion. I'll post a feedback once i get chance to view it. Have a great night :)
    Sir John Money replied to Pichart Y. on 21-Aug-11 11:20 PM
    Hello Pichart,

    Thanks again for your assistance. I opened the attachment and ran the macro. It seems to work just perfectly. Now, would you please provide me with some instructional guidance on how I can translate these codes or macro to my data? In other words, I would I apply the macro to run on my ongoing data?

    Also, the range and cell references of my data would be different from what you sample. Could you please also include guide on how I would adjust or edit to fit my data reference? Please see attached sample data file.

    thanks a bunch!
    sirjohn

    EPI001Report.xls.zip
    Pichart Y. replied to Sir John Money on 22-Aug-11 12:01 AM
    Hi SirJohn,

    I can not open your file after unzip the file. Could you please save your file again and resend me...

    Pichart Y.
    Sir John Money replied to Pichart Y. on 23-Aug-11 08:42 PM
    Data Report.xls.zip

    Hey Pichart,

    Here is the attachment again.

    Thank you
    Pichart Y. replied to Sir John Money on 23-Aug-11 09:43 PM
    Hi Sir John,

    I try to open your new file...same problem occured...then is search the information...you can see the problem and  trouble shooting from this link http://support.microsoft.com/kb/967950

    I don't know why, now I use excel 2007, and normally it can open all excel file.
    Do you us Excel on Mac PC...May be, you need to save the file as Excel Ver.2003,

    Or else we can discuss on my sample, then you try to apply to your file, if any problem/question then you can ask line by line? Depends on you...

    Pichart Y.
    Sir John Money replied to Sir John Money on 24-Aug-11 10:48 PM
    Sample File.zip

    Hi Pichart,

    Let's try again, please. By the way, if you don't mind, we could converse over email. It would be much instant for me. You could reach me at exclusivelyforshopping@gmail.com

    Thank you!
    Pichart Y. replied to Sir John Money on 25-Aug-11 04:10 AM
    HI sir John,

    Ah, finally I can open your file...what's the problem then, could you please let me know, really want to know what is the problem...to know and keep for my knowledge.

    Here it is, I don't know if you're familiar with macro...from my last code then we adjust 2 main things.
    1) All the range...include column reference, area for sorting, etc. (the red letter below)
    2) I adjust the formula there for more flexible

    Here's attachment ---->FindDup&delete_macro2.zip

    Try it and Check if the result is what you expect, let me know if any incomplete / needed to be adjust / assistance.

    regards,

    Pichart Y.

    -------------------- Code is Here ---------------------------

    Sub CleanIncompleteRow()

    'Create Index to control row number for original sequence
    Range("O1").Value = "Control Index"
    For i = 2 To Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    If Range("O" & i - 1).Value = "Control Index" Then
    Range("O" & i).Value = 1
    Else
    Range("O" & i).Value = Range("O" & i - 1).Value + 1
    End If
    Next i

    'Sort to get the lastest Posting period, make sure the last post will be kept
    lstRow = Range("A" & Rows.Count).End(xlUp).Row
     Range("A1:O" & lstRow).Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("K2:K" & lstRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:O" & lstRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        End With

    'Find the row those are duplicate ,then delete all except the most new records
    Sheets(1).Range("A:A").EntireColumn.Insert
    Range("A1").Value = "Check"

    For Each cell In Range("A2:A" & lstRow)
    If Application.WorksheetFunction.CountIf(Range("H" & cell.Row & ":H" & lstRow), cell.Offset(0, 7).Value) = 1 Then
    cell.Value = "Keep"
    End If
    Next

    Range("A2:A" & lstRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    Range("A:A").Delete

    'Sort to get the the original sequence
    lstRow = Range("A" & Rows.Count).End(xlUp).Row
     Range("A1:O" & lstRow).Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("O2:O" & lstRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:O" & lstRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        End With
    Range("O:O").Delete

    Range("A2").Select
    End Sub

    Pichart Y. replied to Sir John Money on 26-Aug-11 02:55 PM
    Hi Sir John,

    Ah finally I have finish coding for you...please try it....

    Attachment ---------------->FindDup&delete_macro3.zip

    ---------------------- Here the Code ----------------------------------------
    Sub CleanIncompleteRow()

    ActiveSheet.Name = "WorkSh"
    lstCol = Range(Cells(1, Columns.Count).Address).End(xlToLeft).Column
    lstRow = Range("A" & Rows.Count).End(xlUp).Row

        For Each cell In Range("A1:" & Cells(1, lstCol).Address)
        If cell.Value = "" Then
        cell.Value = "TempFiled" & cell.Column()
        End If
        Next
        
    'Create Index to control row number for original sequence
    Cells(1, lstCol + 1).Value = "Control Index"
    For i = 2 To Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    If Cells(i - 1, lstCol + 1).Value = "Control Index" Then
    Cells(i, lstCol + 1).Value = 1
    Else
    Cells(i, lstCol + 1).Value = Cells(i - 1, lstCol + 1).Value + 1
    End If
    Next i

    'Create temp field for duplication check
    Sheets(1).Range("A:C").EntireColumn.Insert
    Range("A1").Value = "CheckDup"
    Range("B1").Value = "CheckIndex2"
    Range("C1").Value = "CheckIndex1"
    prdCodeCol = Application.WorksheetFunction.Match("Product", Range("1:1"), 0)
    ctryCol = Application.WorksheetFunction.Match("Country", Range("1:1"), 0)
    postPrdCol = Application.WorksheetFunction.Match("Posting period", Range("1:1"), 0)
    ctrlIndexCol = Application.WorksheetFunction.Match("Control Index", Range("1:1"), 0)

    For Each prdCell In Range(Cells(2, postPrdCol).Address & ":" & Cells(lstRow, postPrdCol).Address)
    prdCell.Value = prdCell.Value * 1
    Next

    'Create Check Index 1
    For Each index1Cell In Range("C2:C" & lstRow)
    index1Cell.Value = Cells(index1Cell.Row, prdCodeCol).Value & Cells(index1Cell.Row, ctryCol).Value
    Next

    'Sort to get the lastest Posting period, make sure the last post will be kept
         Worksheets("WorkSh").Range("A1").Sort _
         key1:=Worksheets("WorkSh").Columns("C"), order1:=xlDescending, _
         key2:=Worksheets("WorkSh").Columns(postPrdCol), order2:=xlDescending, _
         Header:=xlGuess
         
    'Create Check Index 2
    For Each index2Cell In Range("B2:B" & lstRow)
    If index2Cell.Offset(0, 1).Value = index2Cell.Offset(-1, 1).Value Then
    index2Cell.Value = index2Cell.Offset(-1, 0).Value
    Else
    index2Cell.Value = Range(Cells(index2Cell.Row, postPrdCol).Address).Value
    End If
    Next

    'Create Check Duplication
    For Each ChkDupCell In Range("A2:A" & lstRow)
    If ChkDupCell.Offset(-1, 0).Value = "CheckDup" Then
    ChkDupCell.Value = "Keep"
    ElseIf ChkDupCell.Offset(0, 2).Value = ChkDupCell.Offset(-1, 2) Then
    If Range(Cells(ChkDupCell.Row, prdCodeCol).Address).Value = ChkDupCell.Offset(0, 1).Value Then
    ChkDupCell.Value = "Keep"
    End If
    Else
    ChkDupCell.Value = "Keep"
    End If
    Next

    'Delete all those duplicate
    Range("A2:A" & lstRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    'Sort to get the the original sequence
         Worksheets("WorkSh").Range("A1").Sort _
            key1:=Worksheets("WorkSh").Columns(ctrlIndexCol), order1:=xlAscending, _
            Header:=xlGuess
    'Clear worksheet back to standard format

    Columns(ctrlIndexCol).Delete
    Range("A:C").Delete
    MsgBox ("Done")
    Range("A2").Select
    End Sub
    ----------------------------------------- end of Code ----------------------------------------

    Pichart Y.