Forum Discussion

Copper Contributor

Apr 07, 2022

Solved

Help for ERM software migration using Excel

Hello everyone. I have a vendor report spit out from Sage100 that sorted our master vendor list vertically; as in, one vendors info is in a row with columns laid out left>right as ID, name/addre...

Apr 07, 2022

This is a first attempt to 'put my money where my mouth is'. It is not elegant, but I was struggling with the irregularities in the data.

LoadDataλ 
= LAMBDA(vendor, idx,
     LET(
        inputCol, INDEX({1, 2, 5, 2, 2, 2, 2, 2, 2, 3, 4}, idx),
        inputEntry, INDEX({0, 0, 0, 1, 2, 3, 4, 5, 6, 0, 0}, idx),
        item, IF(
            inputEntry,
            SplitAddrλ(INDEX(rawData, 2 + 4 * (vendor - 1), 2), inputEntry),
            INDEX(rawData, 1 + 4 * (vendor - 1), inputCol)
        ),
        item
     )
  );

rawData = RawData!$A$5:$E$16;

SplitAddrλ 
= LAMBDA(ref, k,
    INDEX(TEXTSPLIT(ref, , CHAR({44, 10}), FALSE, " "), k)
  );

LoadingDataFromSage.xlsx15 KB

PeterBartholomew1

Silver Contributor

Apr 07, 2022

keonijared

The first choice must be to get hold of the data as near to the source data format as you can get and perform the ETL tasks using Power Query (that is what it was written to do after all).

Failing that, you would need an up to date version of Excel 365. Any regular pattern of data, such as that you show, can be read into one or more multidimensional arrays. In the present case, it looks like only one data dimension is needed, that being the vendor ID. To read any item of data, you need to know the index for the vendor, the number of vendor IDs, the vertical stride length between vendors (that is, 4) and by wrapping the INDEX function within a Lambda function

[e.g. = DATAλ(vendorIndex, columnIndex)

you could return any given item from the data. The address fields are just a little more complicated because that requires TEXTSPLIT.