Delete duplicates by requesting to keep a specific row from the bunch of duplicates

Hi

I've attached a file for your reference.
I'm trying to remove the duplicates on column B ('Amount') and E ('SId') but also want to keep a specific row from all the duplicates. I want to keep the row that has 'EN' in Column G ('Reference qualifier').

I found a way to remove the duplicates (from the 'remove duplicates' function) and realize that Excel always keep the first row of the bunch of duplicates. But I can't figure out a way to keep a specific/designated row. Hope this make sense.
It's something I will have to do on a regular basis. So an easy solution that I can use regurlarly will be appreciate.

Thanks in advance

Hi yolide

Welcome to the Forum ....

Try this workbook

Create a number of Dynamic Named Ranges "DataTable", "Amount", "Bill_Id", "Reference_qualifier", and "Helper_Column"
Note that all the the range heights are based on the height of "DataTable"
e.g.
"DataTable"
Refers to:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,7)

"Bill_Id"

=OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)

Then in Column K ("Helper Column") Row 2

=B2&D2

Drag/Fill Down

In L2 ("Remove Duplicates")

=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX(Helper_Column,MATCH(TRUE,INDEX(ISNA(MATCH(Helper_Column,$L$1:$L1,0)),0),0))))

Drag/Fill Down until a blank is returned

In M2 this array function

=IF(ISNA(INDEX(DataTable,MATCH($L2&"EN",Amount&Bill_Id&Reference_qualifier,0),COLUMN(A$1))),
INDEX(DataTable,MATCH($L2,Amount&Bill_Id,0),COLUMN(A$1)),
INDEX(DataTable,MATCH($L2&"EN",Amount&Bill_Id&Reference_qualifier,0),COLUMN(A$1)))

Confirm with Ctrl+Shift+Enter not just Enter
Drag Down to last row in Column L then Across to Column S

Column T is not required and only left to prove the formula results.

You could do this without named ranges but array functions are best kept to a minimum size to avoid wasteful calculations and dynamic named ranges will do this for you.

Hope this helps.

Macros are not always acceptable, or even permitted in some situations, using native formula is usually a better option where possible.

Try using the workbook as a template.

Paste any new data into Columns B:H then drag Columns K:S to suit.

Then copy Columns M:S Paste Special > Values to a suitable destination if required.

Okay. The principle is the same but because of the size of your data table it is perhaps easier to use a seperate sheet to get the desired result.
In this workbook, I have called the Sheets "Data" & "Result", once the setup is complete these sheet names can be changed and the various formulae will automatically update.

1/. Create the dynamic named ranges. (Formulas > Name Manager)
"DataTable"
Refers to:

=OFFSET(Data!$A$2,0,0,COUNTA(Data!$AH:$AH)-1,COLUMN(Data!AQ$1))

Syntax
OFFSET(reference,rows,cols,height,width)
reference:= Data!$A$2 (This is the top left cell of the table, therefore the next two values are zero, i.e. no offset)
rows:= 0
cols:= 0
height:= COUNTA(Data!$AH:$AH)-1 (This is the number of rows the table will have, the -1 removes the header from the count)
width:= COLUMN(Data!AQ$1) (this returns the last column number in the table, in this case 41)

Data!$AH:$AH is the column that is most likely to have continuous data that defines the height of the table. This is then used to control the height of the other named ranges.

e.g.
"Amount"
Refers to:

=OFFSET(Data!$AC$2,0,0,COUNTA(Data!$AH:$AH)-1,1)

For more on named ranges see this link Contextures - Excel Names -- Excel Named Ranges

and for more techniques see Chip Pearson - Defined Names

2/. With Sheet "Data" in the "Helper Column" (Column AQ) concatinate "Amount by Line Item" & "Shipment Id"
In AQ2

=AC2&AH2

Drag/Fill Down to the last row in your table.
In AR2 "Remove Duplicates"

=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX(Helper_Column,MATCH(TRUE,INDEX(ISNA(MATCH(Helper_Column,$AR$1:$AR1,0)),0),0))))

Drag/Fill down until a blank is returned.
This will remove the duplicate strings.
This only works for Text values. Numbers and mixed data require different formulae, but as we have concatinated the helper column then the results are text strings.

3/. With Sheet "Result"
Either paste the headers, or in A1

=Data!A$1

Drag/Fill Right to Column AO (i.e. - the last Column in Sheet "Data")

In A2 this array formula, given you are using 2007

=IFERROR(IF(ISNA(INDEX(DataTable,MATCH(Data!$AR2&"EN",Amount&Shipment_Id&Reference_qualifier,0),COLUMN(A$1))),
INDEX(DataTable,MATCH(Data!$AR2,Amount&Shipment_Id,0),COLUMN(A$1)),
INDEX(DataTable,MATCH(Data!$AR2&"EN",Amount&Shipment_Id&Reference_qualifier,0),COLUMN(A$1))),"")

Confirm with Ctrl+Shift+Enter not just Enter.
Drag this down until a blank cell is returned, then Right to Column AO.

Zeros are returned for empty and zero cells with the sample you have provided, these can be suppressed with Number Formatting where appropriate.
I have formatted the first three columns of Sheet "Result" to demonstrate the result
Select the appropriate columns then
Home > Format Cells... Number > Custom
Type:

#,##0;-#,##0;"";@

4/. Finally copy Sheet "Result" and Paste > Paste Special > Values to a suitable destination if required.

All this can of course be done with a macro, but you will, in my opinion, learn more by understanding native formula, rather than using VBa.
Changes are easier with this template using native methods, it can often be an endless task using VBa.

To use Paste your new data into Sheet "Data" and drag the formulae to suit.

Sorry about the rant, but it should stand you in good stead if you can follow and understand the steps.

[EDIT]
With 10,000 rows it will take some time to calculate, but so probably will a VBa solution.
For a task that only has to be done once a week is this really a problem?

Once the formulae have been expanded to cover your typical table size and retained as a template, it should just be a matter of pasting and having a coffee ... ...

Hmm?
I didn't notice this possibility before.

What is the full list of values in "Reference qualifier" like?
Is "EN" alphabetically the first in this list?

If so then Sort the Data By:=
"Amount by Line Item" Order:= smallest to largest.
Add a level
Then by
"Shipment Id" Order:= smallest to largest.
Add a level
Then by
"Reference qualifier" Order:= A-Z

Then Data > Remove Duplicates
Select and check "Amount by Line Item" and "Shipment Id"

If this is possible it will beat any formula or VBa hands down!!!