# Listing data

#### KaiKai

##### Board Regular
Hi!

I have a question, which, of course, could be (quite) easily done by using an extra column.
But can it be done withOUT using an extra column?
This data is in A1:A3: aaa,bbb,ccc
This data is in B1:B3: 4,1,3
This is supposed to be the output in column C:
aaa
aaa
aaa
aaa
bbb
ccc
ccc
ccc

i.e.: list the data of A as often as it is set in B?
Is that possible?

Thank you!

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### facethegod

##### Well-known Member
Book1
ABCD
1aaa4aaa
2bbb1aaa
3ccc3aaa
4aaa
5bbb
6ccc
7ccc
8ccc
Sheet1

Try in C1 drag down...

=INDEX(\$A\$1:\$A\$3,ROWS(\$A\$1:\$A\$3)-SUMPRODUCT(--(ROW(A1)-ROW(\$A\$1)+1<=SUBTOTAL(9,OFFSET(\$B\$1,,,ROW(\$B\$1:\$B\$3)-ROW(\$B\$1)+1,1))))+1)

#### KaiKai

##### Board Regular
Wow!

Within 7 minutes!
It does a great job on my example.
You'll have a feedback when I checked it on my original data.
I'll try to understand your formula, but this may take a little bit longer...

Thank you so much!

#### dafan

##### Well-known Member
If you want to try and understand the formula, cut in into smaller parts in separate parts so you can see what each part does to your data.

For example, if you'd use
=AVERAGE(SUM(A1:A2),SUM(B1:B2))

you could cut this up into SUM(A1:A2) in C1, SUM(B1:B2) in C2 and lastly into AVERAGE(C1,C2) in C3.

##### MrExcel MVP
"I'll try to understand your formula, but this may take a little bit longer..."

As a start, index() returns a value from a certain position in a range, In this case the range is a1:a3 & we want to return the items from the following positions: 1;1;1;1;2;3;3;3

so the

"ROWS(\$A\$1:\$A\$3)-SUMPRODUCT(--(ROW(A1)-ROW(\$A\$1)+1<=SUBTOTAL(9,OFFSET(\$B\$1,,,ROW(\$B\$1:\$B\$3)-ROW(\$B\$1)+1,1))))+1"

...bit is what's constucting the series 1;1;1;1;2;3;3;3. Have a think about how that might be working & post back if you want it unpacked...

#### KaiKai

##### Board Regular
Hi!

I can do with INDEX.
I can do with OFFSET, too.
But I'm quite embarrassed about SUBTOTAL...
9 means SUM, I know, but I'm still trying to figure it out...

By the way:
"-ROW(\$A\$1)+1" and "-ROW(\$B\$1)+1" always returns zero, so it doesn't make sense, does it?

#### KaiKai

##### Board Regular

I'll have a look at it tomorrow, 'cause it's late in Europe...
I know this one in German (hope to translate it quite correctly):
They once found an epsilon, that was SO small, when taken to the power of two, it was lower than zero...

Replies
1
Views
222
Replies
3
Views
991
Replies
4
Views
2K
Replies
1
Views
112
Replies
0
Views
73

1,191,707
Messages
5,988,223
Members
440,139
Latest member
ngaicuong2017

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back