Listing data

[KaiKai]

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!

 

[facethegod]

Book1
	A	B	C	D
1	aaa	4	aaa
2	bbb	1	aaa
3	ccc	3	aaa
4			aaa
5			bbb
6			ccc
7			ccc
8			ccc
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]

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]

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.

 

[PaddyD]

"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]

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?

 

[PaddyD]

the subtotal(..offset()) behaviour is not something you're likely to work out on your own, so here's an explanation:

http://j-walk.com/ss/excel/eee/eee001.txt

...the rest I'll leave to you for now.

 

[KaiKai]

Hi Paddy!

Thanks for the link!
I'll have a look at it tomorrow, 'cause it's late in Europe...
According to your signature:
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...