Unique value from table listed with criterias


Board Regular
Nov 30, 2016
Office Version
  1. 365
I'm trying to figure out how to get the unique set of values into my list using the 'City' and 'Zone' as a criteria.
I can't use advanced filters it needs to be coded. Later on I will add a VBA code that goes thrue all the different cities, and prints them.
I've tried different INDEX(MATCH formulas without any success. Any pointer would be really helpful.


Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Have you tried creating a helper column that combines the two values? You can use Index/Match by combining the City/Zone from the two cells and searching the helper column.
Upvote 0
I can join the two tables, but how would the code be like so that I only got the unique values?
Upvote 0
I guess I need to take a step back and look at the bigger picture.

What is your result? Do you want to find all of the lines that match a City/Zone combination? Does your criteria change often enough so that you need to create code?

Here's an offer to see which direction you want to go...

Create two cells that contain the City and Zone Criteria. Use a helper column that combines those two criteria and checks each line to see if they match. The formula will result in TRUE or FALSE. Then you can simply use a filter to screen out the FALSE ones.

Let's say your City values are in column H and your Zone values are in Column J. Also, you created named ranges for the City and Zone Criteria named CityCrit and ZoneCrit respectively:

Upvote 0
My end result is that the 'Unique barcode' in K is going to use a Barcode font so that it can be IR scanned.
I did join City and Zone. But how do I manage to list out the unique results from barcode? I'm guessing the helper column is suppose to help me count the number of unqies? But the problem is the Unique barcodes can appear in different places. For example Row 2 and 8 has the same barcode. So I can't match em up with the lines below or above.
Sorry if I am confusing. :)
Upvote 0
I want to help. I'm going to need more information. I've worked with barcodes before. Don't you want every single barcode to be different?

Please tell me what you want as your final product. I thought you wanted to be able to find all the unique City/Zone combinations. But I couldn't tell if you needed one certain combo and then another, and so on; or you wanted just to get a new list of unique combos.

Upvote 0
The orange box is the final product.
When ever I change the CITY and ZONE, Every unique barcode for those two matches should get listed in the red field.
So the code should be in K5 and continue down. So K5 should print out '151612' , K6 '151613' , K7 '151619'.

If I we're to switch Zone to 'LAN' in K3, The Unique Barcode list should say. '151511' in K5 and K6 should type out '151236'.
Switching City to 'Pluto' in K2 would output '163215' in K5 , '132168' K6 etc.

The City/Zone is entered manualy by me. The Table is a database that I download.
Hope that makes it clearer
Upvote 0
How about

Excel 2013/2016
Cell Formulas
K5{=IFERROR(INDEX($C$2:$C$16, MATCH(0, IF(($A$2:$A$16=$K$2)*($E$2:$E$16=$K$3), COUNTIF($K$4:$K4, $C$2:$C$16)),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
Upvote 0
Worked like a charm! Thank you very much Fluff.

And thank you Jeff for your effort. I'll try and be clearer in the future :)
Upvote 0
You're welcome & thanks for the feedback
Upvote 0

Forum statistics

Latest member

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