Jun 23 2022 01:56 AM
I made a dashboard tab where I have a an input cell that contains a list of cells from the data validation tool. This is my source of lookup value. It looks like this:
My table array looks something like this:
I wish to input the Commercial Invoice No. to the input cell mentioned above and the result would display the corresponding information for all columns in the dashboard. However most columns have multiple cells that are included in my search. The "SKU" Column for example has multiple cells that needs to be shown. I know merging can be problematic, but I do not know how else to organize the data table. Tips would be highly appreciated! TIA!
Jun 23 2022 08:36 AM
You say
I know merging can be problematic, but I do not know how else to organize the data table.
And with that acknowledgment you've put your finger on the problem. A table, an Excel table, does NOT work with merged rows for such things as Invoice numbers or LOT, etc. It may "look nice" to the human eye, but it totally interferes with the computer's abilities--which are many--to parse the data as you now want to do.
How else to organize? Repeat the Invoice number and Sales contract...whatever needs to be done. Basically, you need to reorganize your data into a standard Excel Table. Then you'll probably find that FILTER (a new relatively new function that does require the most current version of Excel) will work wonders for you. But start with reorganizing your data.
Herewith, some references for you to research.
On Tables: https://exceljet.net/glossary/excel-table
On FILTER: https://www.youtube.com/watch?v=9I9DtFOVPIg
Jul 24 2022 09:22 PM
@mathetes I appreciate your comment a lot! Yes I do think I have to fix my data first and get rid of all the merged cells. I will look into the links you've posted. Once again thank you!
Jul 25 2022 01:07 AM - edited Jul 25 2022 01:11 AM
@JaromV Following up @mathetes post.
What you see is of the form of the first table but what Excel sees is the second
What you actually want is more like
To do this, I used a couple of Lambda functions
FillTableλ = LAMBDA(tbl,
LET(
fInv, FillDownλ(CHOOSECOLS(tbl, 1)),
fSKU, FillDownλ(CHOOSECOLS(tbl, 2)),
fPort, FillDownλ(CHOOSECOLS(tbl, 3)),
HSTACK(fInv, fSKU, fPort, Container)
)
);
FillDownλ = LAMBDA(item,
SCAN("Hdr", item, LAMBDA(last, curr, IF(curr <> "", curr, last)))
);
At this point, you may wonder whether the result is worth the effort? The next step is simpler though.
The worksheet formula
= LET(
filledTable, FillTableλ(table),
inv, TAKE(filledTable,,1),
filtered, FILTER(filledTable, inv=selectedInv),
DROP(filtered,,1)
)
gives
so it is possible to make progress.
Note: it would be more efficient to apply the filter earlier in the process but that adds complexity because the port name would not necessarily lie within the filtered dataset.
Jul 25 2022 06:34 AM
The file demonstrates some of the ideas but, at the current time, is only accessible to Excel 365 Insider beta users.
The worksheet formula probably could do with some tidying up
= LET(
filledTable, FillTableλ(table),
invoice, TAKE(filledTable,,1),
filtered, FILTER(filledTable, invoice=selectedInv),
data, DROP(filtered,,1),
SKUs, TEXTJOIN(¶,,TAKE(data,,1)),
port, UNIQUE(CHOOSECOLS(data,2)),
containers, TEXTJOIN(¶,,CHOOSECOLS(data,3)),
VSTACK(SKUs,port,containers)
);
¶ = "," & CHAR(10);