2D Match - Index and Match with more tha one row.

[Penacare]

Hi,

I am looking for a way to use a lookup values to match the Chemical concentration with its respective Sample#: For Chemical 1 to 9 I don't have a problem, I can easily use 2D lookup Match& Index. However, for Chemical 10 and Chemical 11 this won't work as there are several rows. I could simply manually put all the values in one row. However, there are many chemicals o tha twill take a long time. Any help will be appreciated.

 

[KRice]

Is there any reason to keep the concentrations on different rows for the same chemical? I'm assuming the table values are measurement results?...Do the different rows indicate different dates, measurement methods, labs, operators, etc.? It would be more convenient to transform the table into single rows per chemical, which could be done easily in Power Query, but a clearer idea about the objective would be helpful. What are you trying to match...e.g., what information is known, and what do you want the formula to return?

 

[Penacare]

Thanks, for the reply. Yes, you are correct there are multiple rows for certain chemicals as some might be obtained using different methods or were measured in different dates. This information is provided by the Lab. SO I dont have control on how they provide the data.

What I am trying to create is a easier way to populate my own design tables by using an index match formula so I dont have to go over the 100s reports and do it manually. I want to create a sheet where I can just by copy and paste the lab report and it will automatically populate my tables.

Regards.

 

[KRice]

One idea is to revise the table structure entirely in an effort to flatten it further. But it sounds as if your needs from the table may not depend on the details that led to the information being placed on different rows to begin with. Another approach is to take the table provided, drop it into a formal Excel table and use Power Query to transform it into a flatter structure so that conventional lookup formulas can be used. I took a representative sample of your input data, converted it to an Excel table (by clicking anywhere in the data and hitting Ctrl-t and indicating that the table has headers). Then I created a Power Query script that effectively merges the rows, transforming this initial table:

MrExcel_20220511.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Chemical	Sample 1	Sample 2	Sample 3	Sample 4	Sample 5	Sample 6	Sample 7	Sample 8	Sample 9	Sample 10
2	Chemical 1	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5
3	Chemical 2	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4
4	Chemical 3	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5
5	Chemical 4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4
6	Chemical 5	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3
7	Chemical 6	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4
8	Chemical 7	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3
9	Chemical 8	72	76	71	78	78	71	75	76	80	70
10	Chemical 9	<30	<30	<30	<30	<30	<30	<30	<30	<30	<30
11	Chemical 10	<0.01	<0.01	<0.01	<0.01	<0.01			<0.01	<0.01	<0.01
12							<0.5	<0.5
13	Chemical 11									239	1020
14		1020		1140			1290
15					750
16			1190			291		463	236
17	Chemical 12	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5
SourceData

...into this:

MrExcel_20220511.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Chemical	Sample 1	Sample 10	Sample 2	Sample 3	Sample 4	Sample 5	Sample 6	Sample 7	Sample 8	Sample 9
2	Chemical 1	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5
3	Chemical 10	<0.01	<0.01	<0.01	<0.01	<0.01	<0.01	<0.5	<0.5	<0.01	<0.01
4	Chemical 11	1020	1010	1190	1140	750	291	1290	463	236	239
5	Chemical 12	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5
6	Chemical 2	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4
7	Chemical 3	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5	<0.5
8	Chemical 4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4
9	Chemical 5	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3
10	Chemical 6	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4	<0.4
11	Chemical 7	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3	<0.3
12	Chemical 8	72	70	76	71	78	78	71	75	76	80
13	Chemical 9	<30	<30	<30	<30	<30	<30	<30	<30	<30	<30
Transformed

The M-code for doing this, for the main body, Query Table1:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Chemical", type text}, {"Sample 1", type any}, {"Sample 2", type any}, {"Sample 3", type any}, {"Sample 4", type any}, {"Sample 5", type any}, {"Sample 6", type any}, {"Sample 7", type any}, {"Sample 8", type any}, {"Sample 9", type any}, {"Sample 10", type any}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Chemical"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"Chemical"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Chemical", Order.Ascending}, {"Attribute", Order.Ascending}, {"Value", Order.Ascending}}),
    #"Appended Query" = Table.Combine({#"Sorted Rows", Columns}),
    #"Removed Duplicates" = Table.Distinct(#"Appended Query"),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attribute]), "Attribute", "Value"),
    #"Filtered Rows1" = Table.SelectRows(#"Pivoted Column", each ([Chemical] <> null)),
    #"Sorted Rows1" = Table.Sort(#"Filtered Rows1",{{"Chemical", Order.Ascending}})
in
    #"Sorted Rows1"

And for the column headings needed in an append operation, Query Columns:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Chemical] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Chemical"}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Removed Columns"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type text}, {"Column9", type any}, {"Column10", type any}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type1",1),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Chemical", each null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Value", each null),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Column1", "Attribute"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Chemical", "Attribute", "Value"})
in
    #"Reordered Columns"

A working sample file is here, if you'd like to try it out. Once new information is added to the table on the SourceData sheet, hit Data>Refresh All to generate a new output table on the Transformed sheet.

MrExcel_20220511_Penacare.xlsx

Shared with Dropbox

www.dropbox.com