Forum Discussion
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/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.
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 Replies
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 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.
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;
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.
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?
- 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.- 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.
