Unique value from table listed with criterias

[Weeble]

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.
https://imgur.com/a/gnX6R1h

 

[Jeffrey Mahoney]

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.

 

[Weeble]

I can join the two tables, but how would the code be like so that I only got the unique values?

 

[Jeffrey Mahoney]

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:
=if(H2&J2=CityCrit&ZoneCrit,TRUE,FALSE)

Jeff

 

[Weeble]

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.

 

[Jeffrey Mahoney]

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.

Jeff

 

[Weeble]

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

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1	City	Order	Barcode	Artivcle	Zone
2	Atlantis	123	151612	abc	OCE						Atlantis
3	Atlantis	123	151613	abc	OCE						OCE
4	Atlantis	123	151511	abc	LAN
5	Atlantis	123	151234	abc	CRA						151612
6	Atlantis	123	151236	abc	LAN						151613
7	Atlantis	123	151612	abc	OCE						151619
8	Atlantis	123	151612	abc	OCE
9	Atlantis	123	151619	abc	OCE
10	Pluto	321	163215	abc	LAN
11	Pluto	321	132168	abc	LAN
12	Pluto	321	133521	abc	LAN
13	Pluto	321	132168	abc	LAN
14	Pluto	321	132168	abc	LAN
15	Pluto	321	132168	abc	LAN
16	Pluto	321	132168	abc	LAN
Quote

Cell Formulas
Range	Formula
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.

 

[Weeble]

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

 

[Fluff]

You're welcome & thanks for the feedback