Forum Discussion

keonijared's avatar
keonijared
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...
BI & Data Analysis
excel
office 365
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Apr 07, 2022

    keonijared 

    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)
  );

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 07, 2022

keonijared 

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)
  );

 

  • keonijared's avatar
    keonijared
    Copper Contributor
    Apr 07, 2022

    PeterBartholomew1 Thank you so much guys. This got me close to where I needed- issue was that it isn't a constant spacing between vendor IDs, as some had extra rows with random contacts that staff may or may not have added to a given vendor into Sage throughout the years. 

     

    Plus, I tried rerunning the report in different ways, and there are so many random fields in this database that you have no control over how it lays out the exports for Excel, and CSV was a trainwreck attempting to do.

     

    The Lambda wrap is a genius idea, and I definitely didn't think of that. I ended up running some of your functions, and just brute forcing the rest of the document. The real nightmare that I just plowed through was somehow the phone numbers shifted around, and I had no way of verifying that a phone number was to a certain vendor (outside rough area code knowledge), so- you guessed it- I had to plug every single one into Google to verify it was that business. 

     

    Anyone wanna guess how many 'You're a bot!" messages I got? I'm laughing. 

     

    Thanks to the community at large, everyone is so very helpful and I couldn't be more appreciative. I would be more than happy to buy each of you a coffee or lunch somehow for even attempting to genuinely help me. 

     

    Thanks again.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Apr 07, 2022

      keonijared 

      I am glad that we appear to at least be opening up some options.  If the vendor IDs are irregularly spaced the multiple of 4 idea is out the window but could be replaced by looking up the row numbers from the presence of text.

      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),
        rowNum,     SMALL(IF(ISTEXT(vendorNum), SEQUENCE(ROWS(vendorNum))),vendor),
        item, IF(
            inputEntry,
            SplitAddrλ(INDEX(rawData, 1+rownum, 2), inputEntry),
            INDEX(rawData, rownum, inputCol)
        ),
        item
     )
  );

vendorNum = RawData!$A$5:$A$17;

Resources