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
  1. 365
  2. 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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

davie1982

Board Regular
Joined
Nov 19, 2007
Messages
170
Office Version
  1. 365
  2. 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,909
Office Version
  1. 365
Platform
  1. Windows
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,109,275
Messages
5,527,721
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top