• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help! Merging two tables with same events but slightly different names

andrewchen0019

andrewchen0019

New Member
Hi Excel community,

I need advice and help as I am trying to vlookup the event names from Table A and Table B and link the data from these two sources together. As seen below, all the events from the two sources are the same but they have a slightly difference in the naming of the event name. I am trying to find ways which can link up these two tables using the key words (e.g. Marathon, Swimming and Cycling) but couldn't find any solution. I have a long list of events that I need to map and I am taking these tables as an example to potray the problem I am facing.

Any help and advice is greatly appreciated :)


Table A
Event Name Customer IDAddressSpending
Marathon Day 1
87799​
Ap #713-5352 Augue St. $ 159.00
Marathon Day 2
35556​
Ap #792-1077 Quis Rd. $ 91.00
Marathon Day 3
65523​
309-1218 Mauris St. $ 121.00
Marathon Day 4
23511​
780-1647 Eleifend Road $ 143.00
Swimming Competition (Heats only)
64704​
P.O. Box 760, 1594 Proin Av. $ 76.00
Swimming Competition (Finals only)
26583​
211-3519 Duis Rd. $ 75.00
Swimming Competition (Heats & Finals)
42685​
Ap #563-3459 Nunc St. $ 235.00
Cycling 2020 - Qualifying Round
48936​
1492 Mauris Street $ 283.00
Cycling 2020 - Preliminary Round
78590​
P.O. Box 775, 9106 Neque. Street $ 298.00
Cycling 2020 - Quarter Finals
28069​
P.O. Box 126, 3410 Ornare St. $ 87.00
Cycling 2020 - Semi Finals
41994​
P.O. Box 401, 1619 Quisque Rd. $ 259.00
Cycling 2020 - Finals
68834​
278-8589 Duis Street $ 251.00


Table B
Event Name Event Catergory Hirer Name
MarathonRunABC company
MarathonRunABC company
MarathonRunABC company
MarathonRunABC company
FINA Swimming Competition SwimTYR Company
FINA Swimming Competition SwimTYR Company
FINA Swimming Competition SwimTYR Company
OCBC Cycling 2020CyclingShimano Company
OCBC Cycling 2020CyclingShimano Company
OCBC Cycling 2020CyclingShimano Company
OCBC Cycling 2020CyclingShimano Company
OCBC Cycling 2020CyclingShimano Company
 
andrewchen0019
Would You reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
especially How to get the Best Results at Chandoo.org
... and You'll remember soon one sentence like For the best/fastest results, Upload a Sample File?
... especially an Excel-file, which has Your expected results too.
 
1. Remove duplicates from Table B (VLOOKUP will only find the first anyway)
2. Add a header Category immediately to the right of Spending in Table A
3. Add an Autofilter to Table A
4. Filter the Event Name column using Text Filters, Contains… and type say Marath and click OK. Do a quick visual check that these all belong in the same category.
5. In the Category column, select all the visible cells, type Run in one of the cells, then while holding down the Ctrl key, press Enter. This will enter Run into all the visible cells at once.
6. Go back to the Event Name column and change the filter to cells containing ,say, Cycl. Go back to the Category column, select all visible cells and type Cycling, then Ctrl + Enter
7. At this point you can clear the Event Name column filter, and filter the Category column for blanks (this will show you what you have left to categorise), then go back to the Event Name column and filter for something else, etc.

In this way you should be able to apply a category to all rows quite quickly.
Then you can use your VLOOKUP, (pseudo formula):
=VLOOKUP(Category, Event Catergory column, Hirer Name column number, FALSE)
and
VLOOKUP(Category, Event Name column, Hirer Name column number, FALSE)
You may have to move the Event Catergory (sic) column to the left of Table B (or better, use XLOOKUP)
 
... and we probably need to know the version of Excel. For Excel 365, I can use
Code: 
=  LET(
     n₁, COLUMNS(TableA),
     n₂, COLUMNS(TableB),
     c₁, SEQUENCE(1, n₁+n₂-1),
     c₂, c₁+1-n₁,
     r₁, SEQUENCE(ROWS(TableA)),
     r₂, XMATCH("*"&LEFT(NameA,8)&"*", NameB, 2),
     IF(c₁<=n₁, INDEX(TableA, r₁, c₁), INDEX(TableB, r₂, c₂)) )
where I have implemented a rudimentary search for the corresponding row.
 
andrewchen0019 said:
Hi Excel community,

I need advice and help as I am trying to vlookup the event names from Table A and Table B and link the data from these two sources together. As seen below, all the events from the two sources are the same but they have a slightly difference in the naming of the event name. I am trying to find ways which can link up these two tables using the key words (e.g. Marathon, Swimming and Cycling) but couldn't find any solution. I have a long list of events that I need to map and I am taking these tables as an example to potray the problem I am facing.

Any help and advice is greatly appreciated :)


Table A
Event NameCustomer IDAddressSpending
Marathon Day 1
87799​
Ap #713-5352 Augue St.$ 159.00
Marathon Day 2
35556​
Ap #792-1077 Quis Rd.$ 91.00
Marathon Day 3
65523​
309-1218 Mauris St.$ 121.00
Marathon Day 4
23511​
780-1647 Eleifend Road$ 143.00
Swimming Competition (Heats only)
64704​
P.O. Box 760, 1594 Proin Av.$ 76.00
Swimming Competition (Finals only)
26583​
211-3519 Duis Rd.$ 75.00
Swimming Competition (Heats & Finals)
42685​
Ap #563-3459 Nunc St.$ 235.00
Cycling 2020 - Qualifying Round
48936​
1492 Mauris Street$ 283.00
Cycling 2020 - Preliminary Round
78590​
P.O. Box 775, 9106 Neque. Street$ 298.00
Cycling 2020 - Quarter Finals
28069​
P.O. Box 126, 3410 Ornare St.$ 87.00
Cycling 2020 - Semi Finals
41994​
P.O. Box 401, 1619 Quisque Rd.$ 259.00
Cycling 2020 - Finals
68834​
278-8589 Duis Street$ 251.00


Table B
Event NameEvent CatergoryHirer Name
MarathonRunABC company
MarathonRunABC company
MarathonRunABC company
MarathonRunABC company
FINA Swimming CompetitionSwimTYR Company
FINA Swimming CompetitionSwimTYR Company
FINA Swimming CompetitionSwimTYR Company
OCBC Cycling 2020CyclingShimano Company
OCBC Cycling 2020CyclingShimano Company
OCBC Cycling 2020CyclingShimano Company
OCBC Cycling 2020CyclingShimano Company
OCBC Cycling 2020CyclingShimano Company
Click to expand...
The above tables are presented using dummy variables and values
 
p45cal said:
1. Remove duplicates from Table B (VLOOKUP will only find the first anyway)
2. Add a header Category immediately to the right of Spending in Table A
3. Add an Autofilter to Table A
4. Filter the Event Name column using Text Filters, Contains… and type say Marath and click OK. Do a quick visual check that these all belong in the same category.
5. In the Category column, select all the visible cells, type Run in one of the cells, then while holding down the Ctrl key, press Enter. This will enter Run into all the visible cells at once.
6. Go back to the Event Name column and change the filter to cells containing ,say, Cycl. Go back to the Category column, select all visible cells and type Cycling, then Ctrl + Enter
7. At this point you can clear the Event Name column filter, and filter the Category column for blanks (this will show you what you have left to categorise), then go back to the Event Name column and filter for something else, etc.

In this way you should be able to apply a category to all rows quite quickly.
Then you can use your VLOOKUP, (pseudo formula):
=VLOOKUP(Category, Event Catergory column, Hirer Name column number, FALSE)
and
VLOOKUP(Category, Event Name column, Hirer Name column number, FALSE)
You may have to move the Event Catergory (sic) column to the left of Table B (or better, use XLOOKUP)
Click to expand...


Hi p45cal,

Thanks for your insightful answer! I like you answer but it seems too manual to me becos I have a large database. Is there any ways you think I could formulate merging this 2 tables or use any excel function to make my life easier?
 
Also a Power Query fuzzy match (not reliable) at cell A16 of the attached.
 

Attachments

  • Chandoo46018Merging example and problem(PQ fuzzy).xlsx
    20.8 KB · Views: 4
andrewchen0019 said:
it seems too manual to me becos I have a large database. Is there any ways you think I could formulate merging this 2 tables or use any excel function to make my life easier?
Click to expand...
The technique I suggested of adding a Category column using filters is a really quick way of adding the values in the new column wholesale; it is quick even if there are 10s of thousands of rows, and is a way of guaranteeing the correct categorisation (infinitely more reliable than a fuzzy match). Do you have to do this often? A macro could be written for common filters/categories leaving you just to do the stragglers manually (still with filters though).
 
Hi there,

PFA the work around solution only.

Manual cross check of values is recommended.
 

Attachments

  • Copy of Merging example and problem - Workaround .xlsx
    12.3 KB · Views: 3
andrewchen0019
One sample
> use tables as You've written to use
> because there are not unique data in both table ... have to do as You've do with Your expected results
>> if there would be unique data, then solution would be different
> add those three columns in 'Table B'
> copy those headers
> write those three formulas in columns J to L in row 3
 

Attachments

  • Merging example and problem.xlsx
    14.7 KB · Views: 4
You must log in or register to reply here.
Back
Top