Forum Discussion
Help for ERM software migration using Excel
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) );
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.
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.
- 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.
