Unique value from table listed with criterias

Weeble

Board Regular
Joined
Nov 30, 2016
Messages
90
Office Version
  1. 365
gnX6R1h
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

gnX6R1h
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 30, 2016
Messages
90
Office Version
  1. 365
I can join the two tables, but how would the code be like so that I only got the unique values?
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 30, 2016
Messages
90
Office Version
  1. 365

ADVERTISEMENT

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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 30, 2016
Messages
90
Office Version
  1. 365

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">City</td><td style=";">Order</td><td style=";">Barcode</td><td style=";">Artivcle</td><td style=";">Zone</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Atlantis</td><td style="text-align: right;;">123</td><td style="text-align: right;;">151612</td><td style=";">abc</td><td style=";">OCE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Atlantis</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Atlantis</td><td style="text-align: right;;">123</td><td style="text-align: right;;">151613</td><td style=";">abc</td><td style=";">OCE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">OCE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Atlantis</td><td style="text-align: right;;">123</td><td style="text-align: right;;">151511</td><td style=";">abc</td><td style=";">LAN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Atlantis</td><td style="text-align: right;;">123</td><td style="text-align: right;;">151234</td><td style=";">abc</td><td style=";">CRA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">151612</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Atlantis</td><td style="text-align: right;;">123</td><td style="text-align: right;;">151236</td><td style=";">abc</td><td style=";">LAN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">151613</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Atlantis</td><td style="text-align: right;;">123</td><td style="text-align: right;;">151612</td><td style=";">abc</td><td style=";">OCE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">151619</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Atlantis</td><td style="text-align: right;;">123</td><td style="text-align: right;;">151612</td><td style=";">abc</td><td style=";">OCE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Atlantis</td><td style="text-align: right;;">123</td><td style="text-align: right;;">151619</td><td style=";">abc</td><td style=";">OCE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Pluto</td><td style="text-align: right;;">321</td><td style="text-align: right;;">163215</td><td style=";">abc</td><td style=";">LAN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Pluto</td><td style="text-align: right;;">321</td><td style="text-align: right;;">132168</td><td style=";">abc</td><td style=";">LAN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Pluto</td><td style="text-align: right;;">321</td><td style="text-align: right;;">133521</td><td style=";">abc</td><td style=";">LAN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Pluto</td><td style="text-align: right;;">321</td><td style="text-align: right;;">132168</td><td style=";">abc</td><td style=";">LAN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Pluto</td><td style="text-align: right;;">321</td><td style="text-align: right;;">132168</td><td style=";">abc</td><td style=";">LAN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Pluto</td><td style="text-align: right;;">321</td><td style="text-align: right;;">132168</td><td style=";">abc</td><td style=";">LAN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Pluto</td><td style="text-align: right;;">321</td><td style="text-align: right;;">132168</td><td style=";">abc</td><td style=";">LAN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Quote</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K5</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$C$2:$C$16, MATCH(<font color="Green">0, IF(<font color="Purple">(<font color="Teal">$A$2:$A$16=$K$2</font>)*(<font color="Teal">$E$2:$E$16=$K$3</font>), COUNTIF(<font color="Teal">$K$4:$K4, $C$2:$C$16</font>)</font>),0</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Weeble

Board Regular
Joined
Nov 30, 2016
Messages
90
Office Version
  1. 365
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

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
Top