Microsoft Excel - Sorting multiple values.

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

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.

Thank you Wally Eye for the response. I will try and let you know how it goes.

Thanks again.

Hi Sir John Money,

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.

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.

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.

How about this one, is it Ok for your question?

Hello Pichart, Which one? There is no additional info in your post. Please resubmit with intended info. Thanks.

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.

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

Hi John Money,

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 :)

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

Hi SirJohn,

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

Pichart Y.

Data Report.xls.zip

Hey Pichart,

Here is the attachment again.

Thank you

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.

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!

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

Hi Sir John,