Help for ERM software migration using Excel

[keonijared]

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/address in same column, telephone,  extension, and occasionally a contact name for a vendor- not all vendors have a contact name though. It goes down like this:

Here is the sheet I need to move this data to with a completely different format, as such:

Is this something I can do simply using a function I'm not aware of? I would greatly appreciate any input or advice, as a deadline looms and I'm the sole person tasked with this for a middle-sized company. 

Thank you so much.

[#2]

@keonijared 

Interesting. Let's first see if there's a simple way to do this. Do you have any choices on how you receive that data from Sage100?

I ask because I've seen something similar happen when I get a report from American Express on our monthly charges: since I'm bringing it into Excel, and they give a choice of downloading their data as Excel or as CSV (comma separated value), I naturally picked "Excel"...but somebody at their end thinks it's helpful to make the Excel download "pretty," so they've added colors and formatting, so that the addresses appear as what you're showing, multiple lines!!! It's NOT exported as an Excel table, which is just stupid, frankly, on their part.

But the CSV export from Amex is perfectly useable, and is in the kind of format you are wanting to have.

So my first question: can you get the data from Sage100 as CSV or some other format that is closer to what you need in the first place?

[#3]

Excellent question, and yes- it took us HOURS to pull this data from Sage100; any other export format did not include the data that the ERM software company was requesting. This was literally the only way Sage included the minimum fields of data needed.

To top this off, that list started out as just shy of 10,000 vendor entries, and our team had to delete old/unused vendors manually, one by one, to finally curate the file as it is now. If we attempted to re-run reports, we would have to start from scratch, basically, putting us possible weeks over deadline.

I absolutely abhor Sage's UI.

[#4]

@mathetes - sorry, I am new to this forum, and wanted to make sure I tagged your response properly. Thanks so much for even replying, really.

[#5]

@keonijared 

Well, I wish you well. Looking even more closely at that mess they've given you, it looks even harder given that it would appear at least as if they merged a bunch of cells to make the three lines of address appear as a single (merged) cell. So to read and parse out each line, and then, in the case of the state and zip, different parts of one line......that gets very tricky.

There may be some other Excel experts here who can help. My own skills are more limited

If you haven't done so, though, I'd certainly give Sage100 a call to compliment them on how pretty their data looks and then, getting to the point, complain about how utterly useless it is! Maybe they don't realize it! But you'd think that a company involved in data processing would have one or two people who know that databases work better with addresses broken up into their discrete components. In fact, a call or letter to a senior person (even the President) might get results that you don't get from the folks down the line.

[#6]

Aye @mathetes, I very much appreciate even taking a look. Yes, the issue is that ANY reports Sage100 exports to Excel (data-only, workbook, or otherwise) has these merged cells like you see, and other issues like putting the addresses for each vendor in the same column as the name.... Ugh.

I very much concede that I'm not a master at Sage, but I definitely looked through my options before committing so much time on the report we edited down, and this was the closest thing I could create from it.

I'm attempting now to use Text to Columns, using CTRL+J for carriage return as a delimiter to try and split the name/address column up into different columns, but still running into issues here. At any rate, thank you so much, and I appreciate the well wishes. I hope someone out there looks at this and instantly can say I'm missing something easy here.

[#7]

@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.

[#8 · Accepted solution]

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

[#9]

@Peter Bartholomew 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.

[#10]

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