Query - Filter out multiple bar codes to show only 1 bar code per product.

davie1982

Board Regular
Joined
Nov 19, 2007
Messages
170
Office Version
365, 2019
Hi!

My query is this -
I have 3 columns.

Barcode | Product Type | Product Name | Colour


1234567890123 | AAA | Example1 | White
1234567890124 | AAA | Example1 | Black
1234567890125 | AAA | Example1 | Red
1234567890126 | AAA | Example2 | Purple
1234567890127 | AAA | Example2 | Orange
1234567890128 | AAA | Example2 | Blue
1234567890129 | BBB | Example3 | Green
1234567890130 | BBB | Example3 | Yellow
1234567890131 | BBB | Example3 | Pink
1234567890132 | BBB | Example4 | Grey
1234567890133 | BBB | Example4 | Bright Green
1234567890134 | BBB | Example4 | Navy Blue

I wish to group each product by Product Type and Product Name but I only want 1 bar code showing, doesn't matter which one, and I don't care about the colour.

How can I have a query just select 1 barcode so the data would look like the following?

Barcode | Product Type | Product Name

1234567890123 | AAA | Example1
1234567890126 | AAA | Example2
1234567890129 | BBB | Example3
1234567890132 | BBB | Example4
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

davie1982

Board Regular
Joined
Nov 19, 2007
Messages
170
Office Version
365, 2019
Nvm i figured it out - put 'First' or 'Last' into the grouping field (Total) and it brings only 1 record.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,831
Be very careful with Last or First - it seldom works the way many would expect. Do not use it on an un-ordered set of records or you run the risk of getting what you think is Last sometimes, and sometimes not. Oft said that table records are like a bucket of marbles in that they have no guarantee of an intrinsic order. Sticking with the marbles, think of it this way:
I dumped the marbles in a chute where they could all line up in single file in a trough and the green cat's eye marble was first in line. What guarantee do I have that the same marble will be first next time I do this? Obviously none. This is how Last (or First) can function unless you impose an order on the recordset. I see no definitive evidence of an order in your data example, hence the warning. If you're thinking bar code, and that is a text field, then no.

The sad thing about all this is that it can creep in unnoticed for a long time, thus corrupt data. I have a job to do for someone else to fix this very problem.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,553
Messages
5,469,401
Members
406,648
Latest member
Tucup1

This Week's Hot Topics

Top