I have data being output in a really messy format and the team cut and paste each time to get a pretty version of the report. I offered to help automate but have come across an issue where the offset row number changes for each customer.
My sample file shows the messy data on DATA tab, the end result we are needing on the CLEAN tab. The Formulas tab is my start for this problem.
To start, I got a list of customers and determined we are looking for the Bill To account. This list is output with multiple versions of the same account, so column I has a formula to strip out duplicates. That is:
{=IFERROR(INDEX($B$2:$B$140, MATCH(0,IF(ISBLANK($B$2:$B$140),1,COUNTIF(I$1:I1, $B$2:$B$140)), 0)),"")}
Then column J can be a simple lookup for the name of the customer.
Column K is where I am stuck. Having named my full data table Pronto and column A of my data table AccCode, I now have a formula:
=IFERROR(OFFSET(INDEX(Pronto,MATCH($I2,AccCode,0),3),11,0),0)
The problem is, only the first customer is offset by 11 rows. The next with data in it should only be 10, the next one 12 and the last 13. In each case, the field names are the same so I tried putting another match formula in and it didn't work. Anyone got a way of moving the number of offset rows automatically?
Thanks heaps!
Sandy
My sample file shows the messy data on DATA tab, the end result we are needing on the CLEAN tab. The Formulas tab is my start for this problem.
To start, I got a list of customers and determined we are looking for the Bill To account. This list is output with multiple versions of the same account, so column I has a formula to strip out duplicates. That is:
{=IFERROR(INDEX($B$2:$B$140, MATCH(0,IF(ISBLANK($B$2:$B$140),1,COUNTIF(I$1:I1, $B$2:$B$140)), 0)),"")}
Then column J can be a simple lookup for the name of the customer.
Column K is where I am stuck. Having named my full data table Pronto and column A of my data table AccCode, I now have a formula:
=IFERROR(OFFSET(INDEX(Pronto,MATCH($I2,AccCode,0),3),11,0),0)
The problem is, only the first customer is offset by 11 rows. The next with data in it should only be 10, the next one 12 and the last 13. In each case, the field names are the same so I tried putting another match formula in and it didn't work. Anyone got a way of moving the number of offset rows automatically?
Thanks heaps!
Sandy