Excel Lookup Function help

Question

Hello,

I have an excel output from an ERP system that's not great with functionality. I need to sort the dataset using a function to separate out variables. Currently all variables go into the "TEST RESULT" Column with the "CHARACTERISTIC DESCRIPTION" column acting as the filterable column

I would like to create a new table on sheet 1 where the test result is prefiltered into the different results eg Colour, Haze, Density etc. All of this would be individual to the given "GRN/LOT/PCODE" which is an individual. I am aware the exact case is important for the Colour and ideally would use a wildcard value for colour "Colour" so that I pick up all the options

I've tried a variety of XLookups but none seem to be returning the correct values from the table they will usually return the first value for the given GRN/LOT/PCODE

=XLOOKUP(A:A&"Colour",Query1!C:C&Query1!H:H,Query1!I:I)

Query1 is the exported dataset and sheet 1 is my attempt at getting it to sort

Thanks

Sean

Answer 1

The secret is to build sub-arrays of the data in Query1 that you can then perform lookups on. The FILTER function lets you select all the rows for an individual. The CHOOSECOLS function lets you concentrate on the Description and the Result.

In all the following formulas, replace xxx with row number of the last line of data in Query1.

Start by building the column headers for your new table:

1. In A1 enter =Query1!C1:E1
2. In D1 enter =TRANSPOSE(SORT(UNIQUE(Query1!H2:Hxxx)))

Generate a row for each individual:

1. In A2 enter =UNIQUE(Query1!C2:Cxxx)

Extract the data for the first individual:

1. In B2 enter =VLOOKUP($A2,FILTER(Query1!$C$2:$L$xxx,Query1!$C$2:$C$xxx=$A2),2)
2. Copy this cell into C2 and change the column reference from 2 to 3.
3. In D2 enter =VLOOKUP(D$1,CHOOSECOLS(FILTER(Query1!$C$2:$L$xxx,Query1!$C$2:$C$xxx=$A2),6,7),2,0)
4. Copy this cell into E2, F2, ... to the last column with a header in row 1
5. Copy all of row 2 starting in column B down to the last row with an individual in column A

Answer 2

exported_data-1.csv.txt

select count(*) from import_csv;

create temp table aa as

select P033606,regexp2('Colour|Solid|pH',AnalystName) item,BK from import_csv where regexp('Colour|Solid|pH',AnalystName);

create temp table bb as

select * from aa group by P033606,item;

cli_create_two_dim~bb~item~BK;

select * from bb_two_dim limit 200;

//cli_down_csv~bb_two_dim;