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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Nvm i figured it out - put 'First' or 'Last' into the grouping field (Total) and it brings only 1 record.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top