VLOOKUP/TEXTJOIN (or similar) to return multiple unique results in a single cell

D

DessertDiva

New Member
Joined
Dec 5, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I've got Table 1 with tracking numbers for November. I've got Table 2 with tracking numbers and invoice numbers November. I want to have Table 1 look for its tracking # in Table 2 and return to me all invoice numbers where that tracking # appears and return those results in a single cell. I did find a TEXTJOIN formula that will pull all results in a single cell. YAY! That made me so happy until . . .

A tracking number is listed for every single fee associated with it on any given invoice so this TEXTJOIN formula is returning many invoices numbers in that one cell but only 2 of them are unique. Sometimes a tracking # can appear on 3 or more invoices depending on all the extra charges that come in for it over time. How do I get rid of those duplicates. I would like a formula that will return multiple UNIQUE results into one cell.

Also, do you know how I could do that same formula but have Table 1 find its tracking # and return to me invoice #s from two different tables? Sometimes we'll ship something in November but charges for it will come in across both November and December invoices, sometimes they'll even trickle into January/February invoices, too. See Order #8476 in Table 1 below. That tracking # is appearing on both Nov and Dec UPS invoices.

TIA for your help!!!

Here's Table 1 (Summary of all Order/Shipping Info):
Order #Order DateWarehouseTracking #Carrier FeeIs tracking # on Nov UPS?Is tracking # on Dec UPS?Is tracking # accounted for?UPS Invoice #
826310/31/2020Pink1Z171WF0682449527.74
TRUE​
FALSE​
TRUE​
827711/2/2020Pink1Z171WF0682716422.31
TRUE​
FALSE​
TRUE​
847611/19/2020Pink1Z171WF0673725936.64
TRUE​
TRUE​
TRUE​

Here's Table 2 (November UPS):
Order DateWarehouseShip Service Charge DescriptionTracking #UPS Invoice #
11/4/2020PINKEntry Prep Fee1Z171WF068244951431875072
11/4/2020PINKBrokerage GST1Z171WF068244951431875072
11/4/2020PINKDisbursement Fee1Z171WF068244951431875072
11/4/2020PINKCa British Columbia Pst1Z171WF068244951431875072
11/4/2020PINKCustoms Gst1Z171WF068244951431875072
11/4/2020PINK1Z171WF068244951431875072
11/4/2020PINK1Z171WF068244951431875072
11/2/2020PINKStandard to Canada1Z171WF06824495000000171WF0450
11/2/2020PINKUPS carbon neutral1Z171WF06824495000000171WF0450
11/2/2020PINKFuel Surcharge1Z171WF06824495000000171WF0450
11/3/2020PINKStandard to Canada1Z171WF06827164000000171WF0450
11/3/2020PINKResidential Surcharge1Z171WF06827164000000171WF0450
11/3/2020PINKUPS carbon neutral1Z171WF06827164000000171WF0450
11/3/2020PINKFuel Surcharge1Z171WF06827164000000171WF0450
11/9/2020PINKBrokerage GST1Z171WF068271641432650786
11/9/2020PINKDisbursement Fee1Z171WF068271641432650786
11/9/2020PINKEntry Prep Fee1Z171WF068271641432650786
11/9/2020PINKQST1Z171WF068271641432650786
11/9/2020PINKPst Quebec1Z171WF068271641432650786
11/9/2020PINKCustoms Gst1Z171WF068271641432650786
11/9/2020PINK1Z171WF068271641432650786
11/9/2020PINK1Z171WF068271641432650786
11/23/2020PINKWorldwide Expedited1Z171WF06737259000000171WF0480
11/23/2020PINKPeak Surcharge - Commercial1Z171WF06737259000000171WF0480
11/23/2020PINKUPS carbon neutral1Z171WF06737259000000171WF0480
11/23/2020PINKDuty and Tax Forwarding Surcharge1Z171WF06737259000000171WF0480
11/23/2020PINKFuel Surcharge1Z171WF06737259000000171WF0480


Here's Table 3 (December UPS):
Order DateWarehouseShip Service Charge DescriptionTracking #UPS Invoice #
11/26/2020PINKWW Expedited1Z171WF067372591437220044
11/26/2020PINKDisbursement Fee1Z171WF067372591437220044
11/26/2020PINKSkr1Z171WF067372591437220044
11/26/2020PINKValue Added Tax1Z171WF067372591437220044
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
If your version of 365 has the FILTER function you could use something like below to return data from 1 table. My suggestion would be to put all the data into 1 table adding a date column instead of having different tables for each month.

Book2
ABCDEFGHI
1Order #Order DateWarehouseTracking #Carrier FeeIs tracking # on Nov UPS?Is tracking # on Dec UPS?Is tracking # accounted for?UPS Invoice #
2826310/31/2020Pink1Z171WF0682449527.74TRUEFALSETRUE1431875072, 000000171WF0450
3827711/2/2020Pink1Z171WF0682716422.31TRUEFALSETRUE000000171WF0450, 1432650786
4847611/19/2020Pink1Z171WF0673725936.64TRUETRUETRUE000000171WF0480
5
6Here's Table 2 (November UPS):
7Order DateWarehouseShip Service Charge DescriptionTracking #UPS Invoice #
811/4/2020PINKEntry Prep Fee1Z171WF068244951431875072
911/4/2020PINKBrokerage GST1Z171WF068244951431875072
1011/4/2020PINKDisbursement Fee1Z171WF068244951431875072
1111/4/2020PINKCa British Columbia Pst1Z171WF068244951431875072
1211/4/2020PINKCustoms Gst1Z171WF068244951431875072
1311/4/2020PINK1Z171WF068244951431875072
1411/4/2020PINK1Z171WF068244951431875072
1511/2/2020PINKStandard to Canada1Z171WF06824495000000171WF0450
1611/2/2020PINKUPS carbon neutral1Z171WF06824495000000171WF0450
1711/2/2020PINKFuel Surcharge1Z171WF06824495000000171WF0450
1811/3/2020PINKStandard to Canada1Z171WF06827164000000171WF0450
1911/3/2020PINKResidential Surcharge1Z171WF06827164000000171WF0450
2011/3/2020PINKUPS carbon neutral1Z171WF06827164000000171WF0450
2111/3/2020PINKFuel Surcharge1Z171WF06827164000000171WF0450
2211/9/2020PINKBrokerage GST1Z171WF068271641432650786
2311/9/2020PINKDisbursement Fee1Z171WF068271641432650786
2411/9/2020PINKEntry Prep Fee1Z171WF068271641432650786
2511/9/2020PINKQST1Z171WF068271641432650786
2611/9/2020PINKPst Quebec1Z171WF068271641432650786
2711/9/2020PINKCustoms Gst1Z171WF068271641432650786
2811/9/2020PINK1Z171WF068271641432650786
2911/9/2020PINK1Z171WF068271641432650786
3011/23/2020PINKWorldwide Expedited1Z171WF06737259000000171WF0480
3111/23/2020PINKPeak Surcharge - Commercial1Z171WF06737259000000171WF0480
3211/23/2020PINKUPS carbon neutral1Z171WF06737259000000171WF0480
3311/23/2020PINKDuty and Tax Forwarding Surcharge1Z171WF06737259000000171WF0480
3411/23/2020PINKFuel Surcharge1Z171WF06737259000000171WF0480
Sheet1
Cell Formulas
RangeFormula
I2:I4I2=TEXTJOIN(", ",1,UNIQUE(FILTER($E$8:$E$34,$D$8:$D$34=$D2,"")))
 
Upvote 0
@AhoyNC - I can't combine all the UPS data into one table for tracking/billing purposes. It's easier to keep them separate.

The formula you provided works perfectly. Now I just need to figure out how to get it to work across multiple tables. Thank you for your assistance.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
TEXTJOIN return UNIQUE values with INDEXMATCH multiple criteria
Replies
2
Views
654
WhimsicalRaptor87
W
Sufiyan97
  • Question
Partial Text Lookup
2
Replies
18
Views
2K
Sufiyan97
Sufiyan97
S
  • Solved
2 TEXTJOIN problems - remove duplicate/only unique output, and remove '0' from conditional IF results
Replies
3
Views
2K
Ray_Dem
R
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
L
  • Question
Dynamic SUM of multiple named tables (structured referencing)
Replies
2
Views
911
leolapa
L

Forum statistics

Threads
1,214,601
Messages
6,120,465
Members
448,965
Latest member
grijken

Share this page

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