Formula to Extract Unique Data

Hi. Thanks - see attached sample data.

Originally Posted by FDibbins

I suggest you import that text file using csv, to break out the different column values. you could work with it like that, but it would require a bunch of formulas to separate the text string into columns

I think I'm just looking for a way to revise my current formula not necessarily modifying my text file, I mean I'm very close to getting the results that I need, you can see that by comparing the results in the "TB" sheet vs "Expected Results" sheet. I just need help tweaking the formula so that if the GL from the text file matches the GL from the "Rules" sheet (D3:D6) then it return the corresponding SGL (E3:E6).

I am using an ERP system called SAP and downloading finance data into Excel.
The finance data is keyed on General Ledger account (GL) number. For whatever reason, the GL numbers in the system do not group the data as we would like for reporting. So, we have invented a new number (SGL) which defines the "correct" groupings.
What is now needed is a mapping from GL to SGL via using extraction formula.

If I create a pivot table after your text to column method it would give the incorrect results, because as I mentioned per the "Rules" sheet certain GL's roll up to SGL's. For example, 102400 rolls up to 101000 (Also see my note on post #6). Put your data in a pivot and see if you get the same results as the "Expected Results" sheet.

My data is much larger and once I have my formula corrected it will be part of a larger formula that will do different reconciliations based on a drop-down validation list. Thus, I prefer to stick with a single formula to do the extraction. I am almost there, you can see it by comparing my results from the "TB" sheet with the results from the "Expected Results" sheet.

Thanks.

I just saw that you created the pivot table. You can see your results are different from my expected results. Since my data is larger and I have many different worksheets, it will slow down calculation process if I have so many different formulas.

Thanks - I've tried the route your are speaking of but I have many sheets in my workbook and it will take a very long time to do the calculations/processing. Is there anyway to revise my formula in the "TB" sheet to get the expected results? I would like to avoid helper columns as much as possible.

Originally Posted by FDibbins

why? they can save a LOT of calc, and keep formulas simple...they can be hidden if needed

It's sort of complicated for me to explain, but I need to have a clean sheet with just one big formula. The formula once fixed is part of a bigger nested if formula that does a reconciliation based on a drop-down. Generally, hiding formulas still affects the processing/opening/calculations of workbooks.

I have a script that backups the file via windows task scheduler from the server, so no worries about having to repair anything. My big nested array formula is not complete yet but I know it'll be faster, because if you base it on a drop-down, when the IF formula's return a false, then the rest of the formula's don't get calculated, thus does not affect the speed of my worksheet/workbook.

I'm also saving on the file size of my workbook if I don't create helper columns etc.

I'm assuming it's too complicated to update my formula to get the results I want.....

Thanks.

Can anyone fix my formula?

You can see that when you highlight and evaluate (press F9) the beginning part of formula (see below) the unique count is off. It results in 5 instead of 4 since there are 4 unique GL's as you can see in the "Expected Results" sheet. This is because again GL 1024000 rolls up to 101000 per the "Rules" sheet.

SUM(IF(FREQUENCY(MATCH(MID(VAATB,Rules!$I$3,Rules!$G$3),MID(VAATB,Rules!$I$3,Rules!$G$3),0),ROW(VAATB)-ROW($A$2)+1),1))

Thanks in advance.

**BUMP***

Anyone??

My last call. If anyone can help.

Thanks.