I have large repetitive list in excel that I'd like to show in one column

[surfinlakkie]

Good day! Any assistance will be much appreciated
I've received a few giant data lists from SARS. Unfortunately, it looks something like this:
in column A, the folowing list repeats itself about 100 times:
Tax Reference
PRN Number
Date
Capital
Interest
Penalties
Additional Tax Amount
Total

In Column B, there are different values each time for each of the labels in column A
Is there any way I can change all the repetitive data in column A into a single header row, with Column B data listed underneath in rows that can be calculated?
I've tried with a pivot table, but it is not working out for me.
Thank you in advance!

[Glenn Kennedy]

Hi there... and welcome to the Excel Forum.

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[surfinlakkie]

Hi there... and welcome to the Excel Forum.

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Good day Glenn,
Thank you for your reply. I have attached sample data. Thanks so much again!

[Glenn Kennedy]

Assuming your headers run from E2 to L2, use this in E3. coipied across and then down.

=IFERROR(IF(COLUMNS($A:A)<=8,INDEX($B$2:$B$81,COLUMNS($A:A)+(ROWS($1:1)-1)*8),""),"")

[surfinlakkie]

Dear Mr Glenn Kennedy,
I bow down to your genius :D
THANK YOU!

Now I have to employ "the Google" to figure out WHY this is such a fantasmagoric solution, and why I have never had to use IFERROR...

(FYI: i have no idea what is going on in that formula, or why it works, but i will, soon)

Thank you thank you thank you from a frikkin hot South Africa!

[Glenn Kennedy]

A good challenge for you to try to figure it out. If you get stuck: please ask.

the best way to look at it is to go to formulas/evaluate formula and then step through the formula stage-by-stage. Irritatingly, the box can't be expanded; but it's HUGELY useful.

You're welcome... from a frikkin cold Ireland. Oh well, off to Delhi in a week or so for a few days, so hopefully a bit warmer there...

[surfinlakkie]

A good challenge for you to try to figure it out. If you get stuck: please ask.

the best way to look at it is to go to formulas/evaluate formula and then step through the formula stage-by-stage. Irritatingly, the box can't be expanded; but it's HUGELY useful.

You're welcome... from a frikkin cold Ireland. Oh well, off to Delhi in a week or so for a few days, so hopefully a bit warmer there...

Good day once again, Mr Kennedy!

Thanks again for your assistance yesterday.
Thanks for offering to help when I get stuck. So i got stuck :D And since i'd like to use this formula again, and would like to write similar ones, I need some help, if you can spare a moment, Please?

I have gone through the formula and came up with a pretty OK understanding of it, thank you. However, I am stuck on WHY certain elements are there.

=IFERROR(IF(COLUMNS($A:A)<=8,INDEX($B$2:$B$81,,""),"")

What I got from dragging the formula across and down, was essentially this:

~IFERROR is because a normal IF function would return a #REF error when the data is out of range

~ <=8 This is the number of "headings"

~ INDEX looks for the corresponding row item in column B with the formula COLUMNS($A:A)+(ROWS($1:1)-1)*8)

~ COLUMNS($A:A)+(ROWS($1:1)-1)*8) So this counts the columns ► in this case A:A= 1 + (ROWS($1:1)=1-1=0 *8) = 1*0 = 1

So I understand all the parts of the formula with my high-school math and pretty standard Excel formula knowledge.
But, what i dont get, is

Firstly, why do we keep row 1 empty?

Secondly, why do i do the whole ($A:A)+(ROWS($1:1)-1)*8) thing - is there not a shorter way? (yes, i have tried but i cant seem to figure it out, because when i omit row 1, everything jumbles up)

[Glenn Kennedy]

That bit is returning the row number from which the data are drawn.

=COLUMNS($A:A)+(ROWS($1:1)-1)*8

dragged across will go: 1,2,3, to infinity However, because of the IF(COLUMNS($A:A)<=8 bit, once you reach 8, the values are replaced by blanks.

As you drag it down, it increments by multiples of 8. below 1 is 9, below 2 is 10, etc. below 9 is 17, etc....

In effect it creates a grid:

1,2,3,4,5,6,7,8
9,10,11,12,13,14,15,16
17,18,etc

These are the row numbers DOWN from B2 (the starting point) that are to be returned.

i'm not sure what you mean by: Firstly, why do we keep row 1 empty?


Other than the fact that your data began in row 2 in your example.

[surfinlakkie]

thank you! I hope you're a teacher of some sort - this makes sense now, and I will try to apply this in the near future again.

Happy Friday, and enjoy Delhi (*infinitely jealous*)

[Glenn Kennedy]

Mmm. A teacher... of some sort, yes. Not my main job, though, as I have one bad habit. I don't have much patience with fools!!!