Word multiplied by number to create a list.

[nac1987]

Hi there,
Funny one this... easy to explain, perhaps, yet a bit tricky when asked how to solve it! Can anyone help me?

I have 2 columns.

Column A is data, for example a name Nigel, Greg, Betty.
Columb B is a number, besides 'Nigel' will be 3, next to Greg, perhaps 2, Betty has a 5 next to her name, etc.

I want to have a list somewhere else that is being populated by writing out the long-hand version of this data, so it would write:

Nigel
Nigel
Nigel
Greg
Greg
Betty
Betty
Betty
Betty
Betty

With each name repeated by the number, and in a new cell, thanks to someone's previous help I have this sort of working (but I can't attach the file ), but now I'd like it to do the following... can anyone help adjust the formula??

A) Can the formula be adjusted to not display a name at all if a zero is present next to a name?

And an even trickier one...

B) Within the list of names that grows, can there be pre-determined cells to skip?
For example if column D was the big list of names using your formula, could I have previously specified that D39 (and some other specified cells) must be skipped?
So that if I needed 5 x Graham, and it occupied D36, D37, D38, D40, and D41? Because I didn't want it to overwrite what I'd already written in D39.

Thank you so much to anyone that can help, in any way.

 

[nac1987]

The formula I have currently:

Column A - Names
Column B - Numbers

Column D - This:

=IF(INDEX(B:B,MATCH(D1,A:A,0))>COUNTIF( $D$1:D1,D1),D1,OFFSET($A$1,MATCH(D1,A:A,0),0))

Please see above for the 2 extra things I wish it could do!

Thank you again for your time and consideration

 

[XOR LX]

Hi.

=IF(ROWS($1:1)>SUM($B$1:$B$3),"",INDEX($A$1:$A$3,MATCH(TRUE,MMULT(N(ROW($A$1:$A$3)>=TRANSPOSE(N(INDEX(ROW($A$1:$A$3),,)))),$B$1:$B$3)>=ROWS($1:1),0)))

Regards

 

[nac1987]

Hi.

=IF(ROWS($1:1)>SUM($B$1:$B$3),"",INDEX($A$1:$A$3,MATCH(TRUE,MMULT(N(ROW($A$1:$A$3)>=TRANSPOSE(N(INDEX(ROW($A$1:$A$3),,)))),$B$1:$B$3)>=ROWS($1:1),0)))

Regards

This is good! My data is such:

Nigel 3
Greg 2
Betty 5
Bill 0
Graham 19

The formula doesn't show Bill any more But it also won't display Graham now either.

I know I'm a pain...

 

[azumi]

This is good! My data is such:

Nigel 3
Greg 2
Betty 5
Bill 0
Graham 19

The formula doesn't show Bill any more But it also won't display Graham now either.

I know I'm a pain...

XOR formula is array formula, you need to press CTRL+SHIFT+ENTER button together, and then copied down

 

[nac1987]

That's great!

How many questions can I ask before someone shouts at me?

This is almost there.

The formula breaks down with a value error when it finds a blank row (beyond the list of names). Sorry, I'm way out my depth here!

 

[XOR LX]

XOR formula is array formula, you need to press CTRL+SHIFT+ENTER button together, and then copied down

But it's not an array formula!!

 

[XOR LX]

This is good! My data is such:

Nigel 3
Greg 2
Betty 5
Bill 0
Graham 19

The formula doesn't show Bill any more But it also won't display Graham now either.

I know I'm a pain...

If your data isn't actually in the range A1:B3, then you need to adjust the references in my formula accordingly, i.e. $A$1:$A$3 and $B$1:$B$3 to e.g. $A$1:$A$5 and $B$1:$B$5, for a data range of A1:B5.

Regards

 

[nac1987]

If your data isn't actually in the range A1:B3, then you need to adjust the references in my formula accordingly, i.e. $A$1:$A$3 and $B$1:$B$3 to e.g. $A$1:$A$5 and $B$1:$B$5, for a data range of A1:B5.

Regards

Thanks, yes

But if I change the row reference in the formula to say "10", but I only have values in the rows up to 5, it doesn't work

The formula requires 0's in rows 6-10 instead of a blank cell, otherwise it throws a #VALUE? error-fit .

Cheers for the assistance!

 

[Aladin Akyurek]

If this is data...

Row\Col	A​	B​
1​	NAME	VALUE
2​	Nigel	3
3​	Greg	2
4​	Betty	5
5​	Bill	0
6​	Graham	19
7​

in A2:B6, what result do you want obtain from it? Please state clearly the conditions under which the result must obtain.