# Need Help with Match/IF/ISNUMBER problem.

#### MikeRob

##### New Member
I have a problem I'm trying to solve. hopefully I explain this right. I'm trying do find a formula to insert 3 missing columns of data from another data set that has those columns. the data with the missing columns has over 600 rows. and duplicate values.. the data that has the columns i need have no duplicates. I need to get the columns form the non duplicate value dat to match up correctly with but fill in all the duplicate value data. I'm sorry if I have anyone confused so i provided an example that shows what I'm trying to achieve.

the dat set that has the missing columns but contains duplicates is Column A - C

the data that doesn't have duplicates but has the 3 missing columns I need (columns F, I, and J) is in Columns E- J

the 3rd set of data Columns L - Q is the result I'm trying to achieve.

 Muni Ward District Municipality Muni Code Ward District Region Line Up No. Municipality Muni Code Ward District Region Line Up No. ALEPPO 00 01 ALEPPO 101 00 01 4​ 1​ ALEPPO 101 00 01 4​ 1​ ALEPPO 00 01 ASPINWALL 102 00 01 3​ 1​ ALEPPO 101 00 01 4​ 1​ ALEPPO 00 01 ASPINWALL 102 00 02 3​ 2​ ALEPPO 101 00 01 4​ 1​ ALEPPO 00 01 ASPINWALL 102 00 03 3​ 3​ ALEPPO 101 00 01 4​ 1​ ALEPPO 00 01 AVALON 103 01 00 4​ 2​ ALEPPO 101 00 01 4​ 1​ ASPINWALL 00 01 AVALON 103 02 01 4​ 3​ ASPINWALL 102 00 01 3​ 1​ ASPINWALL 00 01 AVALON 103 02 02 4​ 4​ ASPINWALL 102 00 01 3​ 1​ ASPINWALL 00 01 AVALON 103 03 01 4​ 5​ ASPINWALL 102 00 01 3​ 1​ ASPINWALL 00 01 AVALON 103 03 02 4​ 6​ ASPINWALL 102 00 01 3​ 1​ ASPINWALL 00 01 AVALON 103 03 03 4​ 7​ ASPINWALL 102 00 01 3​ 1​ ASPINWALL 00 02 BALDWIN BORO 104 00 01 1​ 1​ ASPINWALL 102 00 02 3​ 2​ ASPINWALL 00 02 BALDWIN BORO 104 00 02 1​ 2​ ASPINWALL 102 00 02 3​ 2​ ASPINWALL 00 02 BALDWIN BORO 104 00 03 1​ 3​ ASPINWALL 102 00 02 3​ 2​ ASPINWALL 00 02 BALDWIN BORO 104 00 04 1​ 4​ ASPINWALL 102 00 02 3​ 2​ ASPINWALL 00 02 BALDWIN BORO 104 00 05 1​ 5​ ASPINWALL 102 00 02 3​ 2​ ASPINWALL 00 03 BALDWIN BORO 104 00 06 1​ 6​ ASPINWALL 102 00 03 3​ 3​ ASPINWALL 00 03 BALDWIN BORO 104 00 07 1​ 7​ ASPINWALL 102 00 03 3​ 3​ ASPINWALL 00 03 BALDWIN BORO 104 00 08 1​ 8​ ASPINWALL 102 00 03 3​ 3​ ASPINWALL 00 03 BALDWIN BORO 104 00 09 1​ 9​ ASPINWALL 102 00 03 3​ 3​ ASPINWALL 00 03 BALDWIN BORO 104 00 10 1​ 10​ ASPINWALL 102 00 03 3​ 3​ AVALON 01 00 BALDWIN BORO 104 00 11 1​ 11​ AVALON 103 01 00 4​ 2​ AVALON 01 00 BALDWIN BORO 104 00 12 1​ 12​ AVALON 103 01 00 4​ 2​ AVALON 01 00 BALDWIN BORO 104 00 13 1​ 13​ AVALON 103 01 00 4​ 2​ AVALON 01 00 BALDWIN BORO 104 00 14 1​ 14​ AVALON 103 01 00 4​ 2​ AVALON 01 00 BALDWIN BORO 104 00 15 1​ 15​ AVALON 103 01 00 4​ 2​ AVALON 02 01 BALDWIN BORO 104 00 16 1​ 16​ AVALON 103 02 01 4​ 3​ AVALON 02 01 BALDWIN BORO 104 00 17 1​ 17​ AVALON 103 02 01 4​ 3​ AVALON 02 01 BALDWIN BORO 104 00 18 1​ 18​ AVALON 103 02 01 4​ 3​ AVALON 02 01 BALDWIN TWP 105 00 01 5​ 1​ AVALON 103 02 01 4​ 3​ AVALON 02 01 BALDWIN TWP 105 00 02 5​ 2​ AVALON 103 02 01 4​ 3​ AVALON 02 01 BELL ACRES 106 00 01 4​ 8​ AVALON 103 02 01 4​ 3​ AVALON 02 02 BELLEVUE 107 01 01 4​ 9​ AVALON 103 02 02 4​ 4​ AVALON 02 02 BELLEVUE 107 01 02 4​ 10​ AVALON 103 02 02 4​ 4​ AVALON 02 02 BELLEVUE 107 02 01 4​ 11​ AVALON 103 02 02 4​ 4​ AVALON 02 02 BELLEVUE 107 02 02 4​ 12​ AVALON 103 02 02 4​ 4​ AVALON 02 02 BELLEVUE 107 03 01 4​ 13​ AVALON 103 02 02 4​ 4​ AVALON 02 02 BELLEVUE 107 03 02 4​ 14​ AVALON 103 02 02 4​ 4​ AVALON 03 01 BEN AVON 108 00 01 4​ 15​ AVALON 103 03 01 4​ 5​ AVALON 03 01 BEN AVON 108 00 02 4​ 16​ AVALON 103 03 01 4​ 5​ AVALON 03 01 BEN AVON HGTS 109 00 01 4​ 17​ AVALON 103 03 01 4​ 5​ AVALON 03 01 BETHEL PARK 110 01 01 5​ 3​ AVALON 103 03 01 4​ 5​ AVALON 03 01 BETHEL PARK 110 01 02 5​ 4​ AVALON 103 03 01 4​ 5​ AVALON 03 02 BETHEL PARK 110 01 03 5​ 5​ AVALON 103 03 02 4​ 6​ AVALON 03 02 BETHEL PARK 110 02 01 5​ 6​ AVALON 103 03 02 4​ 6​ AVALON 03 02 BETHEL PARK 110 02 02 5​ 7​ AVALON 103 03 02 4​ 6​ AVALON 03 02 BETHEL PARK 110 02 03 5​ 8​ AVALON 103 03 02 4​ 6​ AVALON 03 03 BETHEL PARK 110 03 01 5​ 9​ AVALON 103 03 03 4​ 7​ AVALON 03 03 BETHEL PARK 110 03 02 5​ 10​ AVALON 103 03 03 4​ 7​ AVALON 03 03 BETHEL PARK 110 03 03 5​ 11​ AVALON 103 03 03 4​ 7​ AVALON 03 03 BETHEL PARK 110 04 01 5​ 12​ AVALON 103 03 03 4​ 7​

I may not have the right formula types like I described in the thread title.so if that's the case help is definitely needed. Thank you in advance for your help.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### sandy666

##### Banned - Rules violations
maybe Power Query (Get&Transform)
 Municipality Muni Code Ward District Region Line Up No. ALEPPO 101 0 1 4 1 ALEPPO 101 0 1 4 1 ALEPPO 101 0 1 4 1 ALEPPO 101 0 1 4 1 ALEPPO 101 0 1 4 1 ASPINWALL 102 0 1 3 1 ASPINWALL 102 0 1 3 1 ASPINWALL 102 0 1 3 1 ASPINWALL 102 0 1 3 1 ASPINWALL 102 0 1 3 1 ASPINWALL 102 0 2 3 2 ASPINWALL 102 0 2 3 2 ASPINWALL 102 0 2 3 2 ASPINWALL 102 0 2 3 2 ASPINWALL 102 0 2 3 2 ASPINWALL 102 0 3 3 3 ASPINWALL 102 0 3 3 3 ASPINWALL 102 0 3 3 3 ASPINWALL 102 0 3 3 3 ASPINWALL 102 0 3 3 3 AVALON 103 1 0 4 2 AVALON 103 1 0 4 2 AVALON 103 1 0 4 2 AVALON 103 1 0 4 2 AVALON 103 1 0 4 2 AVALON 103 2 1 4 3 AVALON 103 2 1 4 3 AVALON 103 2 1 4 3 AVALON 103 2 1 4 3 AVALON 103 2 1 4 3 AVALON 103 2 1 4 3 AVALON 103 2 2 4 4 AVALON 103 2 2 4 4 AVALON 103 2 2 4 4 AVALON 103 2 2 4 4 AVALON 103 2 2 4 4 AVALON 103 2 2 4 4 AVALON 103 3 1 4 5 AVALON 103 3 1 4 5 AVALON 103 3 1 4 5 AVALON 103 3 1 4 5 AVALON 103 3 1 4 5 AVALON 103 3 2 4 6 AVALON 103 3 2 4 6 AVALON 103 3 2 4 6 AVALON 103 3 2 4 6 AVALON 103 3 3 4 7 AVALON 103 3 3 4 7 AVALON 103 3 3 4 7 AVALON 103 3 3 4 7

Power Query:
``````let
Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Join = Table.NestedJoin(Source1,{"Muni", "Ward", "District"},Source2,{"Municipality", "Ward", "District"},"Table",JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Table", {"Municipality", "Muni Code", "Ward", "District", "Region", "Line Up No."}, {"Municipality", "Muni Code", "Ward.1", "District.1", "Region", "Line Up No."}),
TSC = Table.SelectColumns(Expand,{"Municipality", "Muni Code", "Ward.1", "District.1", "Region", "Line Up No."}),
Ren = Table.RenameColumns(TSC,{{"Ward.1", "Ward"}, {"District.1", "District"}})
in
Ren``````

#### sandy666

##### Banned - Rules violations
or shorter a bit
Power Query:
``````let
Source1 = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
Join = Table.NestedJoin(Source1,{"Muni", "Ward", "District"},Source2,{"Municipality", "Ward", "District"},"Table",JoinKind.LeftOuter),
TSC = Table.SelectColumns(Join,{"Table"}),
Expand = Table.ExpandTableColumn(TSC, "Table", {"Municipality", "Muni Code", "Ward", "District", "Region", "Line Up No."}, {"Municipality", "Muni Code", "Ward", "District", "Region", "Line Up No."})
in
Expand``````

#### MikeRob

##### New Member
or shorter a bit
Power Query:
``````let
Source1 = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
Join = Table.NestedJoin(Source1,{"Muni", "Ward", "District"},Source2,{"Municipality", "Ward", "District"},"Table",JoinKind.LeftOuter),
TSC = Table.SelectColumns(Join,{"Table"}),
Expand = Table.ExpandTableColumn(TSC, "Table", {"Municipality", "Muni Code", "Ward", "District", "Region", "Line Up No."}, {"Municipality", "Muni Code", "Ward", "District", "Region", "Line Up No."})
in
Expand``````
Okay thank you so much... I want to try to apply this but I’m just learning excel this year and haven’t gotten to power query yet. Is there any way you can send any type of instruction on how to implement this? Or is it a copy and paste thing and how I would do that? I can clearly see it works but just need to know how to apply it please. Again thank you wry much for your help... it’s spot on!!

#### sandy666

##### Banned - Rules violations
Copy code from the post#3 then
1. Data tab
2. New Query
3. From Other Sources
4. Blank Query
it will open PQ Editor, go to Advanced Editor and replace all there with copied code, OK then Close&Load
note: be sure your source tables have the same names as in the code or change them suitably

part of the source tables
 Table1 Table2 Muni Ward District Municipality Muni Code Ward District Region Line Up No. ALEPPO 0 1 ALEPPO 101 0 1 4 1 ALEPPO 0 1 ASPINWALL 102 0 1 3 1 ALEPPO 0 1 ASPINWALL 102 0 2 3 2 ALEPPO 0 1 ASPINWALL 102 0 3 3 3 ALEPPO 0 1 AVALON 103 1 0 4 2 ASPINWALL 0 1 AVALON 103 2 1 4 3 ASPINWALL 0 1 AVALON 103 2 2 4 4 ASPINWALL 0 1 AVALON 103 3 1 4 5 ASPINWALL 0 1 AVALON 103 3 2 4 6 ASPINWALL 0 1 AVALON 103 3 3 4 7 ASPINWALL 0 2 BALDWIN BORO 104 0 1 1 1

if you want to know more about Power Query:

Replies
5
Views
219
Replies
2
Views
430
Replies
7
Views
76
Replies
3
Views
100
Replies
5
Views
252

1,147,750
Messages
5,742,981
Members
423,769
Latest member
LongToast

### 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.

### Which adblocker are you using?

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

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