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 data as you've written there, is correct.

I would like for column D to build a list of these names, exactly as the formula does!

Except I'd like to be able to add more names and numbers freely to column A & B.

The formula is constrained by the number of rows. If I change the formula to accept A2:B100 as you've given in your example, it won't work until I set all the values of column B to "0" instead of being just blank cells. It's not a problem I've had before, but I'm not an excel wizard!

 

[Aladin Akyurek]

The data as you've written there, is correct.

I would like for column D to build a list of these names, exactly as the formula does!

Except I'd like to be able to add more names and numbers freely to column A & B.

The formula is constrained by the number of rows. If I change the formula to accept A2:B100 as you've given in your example, it won't work until I set all the values of column B to "0" instead of being just blank cells. It's not a problem I've had before, but I'm not an excel wizard!

I'm asking you what result from this data you want to obtain under what conditions? I'm not asking you anything about any formula!...

 

[nac1987]

I'm asking you what result from this data you want to obtain under what conditions? I'm not asking you anything about any formula!...

Apologies.

Here's what I'm trying to do.

1) have a data set as you've written, that is constantly expanding; more names, more numbers.
2) create a longer list of these names where Nigel-3, writes Nigel, Nigel, Nigel. and Betty-5 writes 'Betty' 5 times. If a name has a 0 beside it, I don't want that name to appear in this ever expanding list at all.

Following on from that, but that's already proving difficult...

3) I'd to have some pre-defined 'gaps' in the list. I think this is probably impossible. If the list of 5 Betty's occupies B4, B5, B6, B7, and B8, could I have said "insert a blank at B7" and then the Betty's would then occupy B4, B5, B6, B8 and B9.
4) Ultimately, I'd like to be able to draw some table-plans. Where Excel takes the long list of names, and organises them into some drawn out tables. Where I will draw a table in a 12x8 grid and then I can say 'the first 8 names in this column, the second 8 names in this column, etc... up to the 12th set of names in this column." - this is why I needed the blanks there. Because in the real-life physical tables, some places must be blank... There will be more names in the list than I can fit on a 12x8 grid, so I'll draw up several like that.

Does that give better scope to what I'm trying to achieve?

 

[Aladin Akyurek]

It's getting harder to communicate.

Are we counfounding what is result and what is source? Did I post a result?

 

[nac1987]

If this is data...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]NAME[/td][td]VALUE[/td][/tr]

[tr][td]

2​

[/td][td]Nigel[/td][td]3[/td][/tr]

[tr][td]

3​

[/td][td]Greg[/td][td]2[/td][/tr]

[tr][td]

4​

[/td][td]Betty[/td][td]5[/td][/tr]

[tr][td]

5​

[/td][td]Bill[/td][td]0[/td][/tr]

[tr][td]

6​

[/td][td]Graham[/td][td]19[/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][/tr]
[/table]

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

[table="width: 500, class: grid, align: center"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[/tr]
[tr]
[td]Nigel[/td]
[td]3[/td]
[td][/td]
[td]Nigel[/td]
[/tr]
[tr]
[td]Betty[/td]
[td]2[/td]
[td][/td]
[td]Nigel[/td]
[/tr]
[tr]
[td]Dave[/td]
[td]0[/td]
[td][/td]
[td]Nigel[/td]
[/tr]
[tr]
[td]Bill[/td]
[td]2[/td]
[td][/td]
[td]Betty[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]Betty[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]Bill[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]Bill[/td]
[/tr]
[/table]

Column D is what I'm after. For now.

 

[Matty]

But it's not an array formula!!

Hi XOR LX,

Interested to know why you went for a non-array formula, which I see harnesses INDEX, and not something akin to this array version, which has less function calls:

=IF(ROWS(D$2:D2)>SUM(B$2:B$5),"",INDEX(A$2:A$5,MATCH(TRUE,MMULT((ROW(A$2:A$5)>=TRANSPOSE(ROW(A$2:A$5)))+0,B$2:B$5)>=ROWS(D$2:D2),0)))

Was there a reason for your choice?

Cheers,

Matty (Also in Yorkshire!)

 

[Aladin Akyurek]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Nigel
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]Nigel
[/TD]
[/TR]
[TR]
[TD]Betty
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]Nigel
[/TD]
[/TR]
[TR]
[TD]Dave
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nigel
[/TD]
[/TR]
[TR]
[TD]Bill
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]Betty
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Betty
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bill
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bill
[/TD]
[/TR]
</tbody>[/TABLE]

Column D is what I'm after. For now.

A different take...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]NAME[/td][td]VALUE[/td][td]CUM[/td][td]

7​

[/td][/tr]

[tr][td]

2​

[/td][td]Nigel[/td][td]

3​

[/td][td]

3​

[/td][td]#List#[/td][/tr]

[tr][td]

3​

[/td][td]Betty[/td][td]

2​

[/td][td]

5​

[/td][td]Nigel[/td][/tr]

[tr][td]

4​

[/td][td]Dave[/td][td]

0​

[/td][td][/td][td]Nigel[/td][/tr]

[tr][td]

5​

[/td][td]Bill[/td][td]

2​

[/td][td]

7​

[/td][td]Nigel[/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td]Betty[/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td]Betty[/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td]Bill[/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td]Bill[/td][/tr]

[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

1) In C2 just enter and copy down:

=IF($B2>0,SUM($B$2:B2),"")

2) Define Lrow by means of Formulas | Name Manager as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$B:$B)

3) Define Name as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

4) Define Value as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)

5) Define Cum as referring to:

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrow)

6) In D1 enter:

=SUM(Sheet1!$B$2:$B$8)

7) In D3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$3:D3)<=$D$1,
  INDEX(Name,MATCH(1,IF(Cum>=ROWS($D$3:D3),
  IF(ROWS($D$3:D3)<=Cum,
  IF(ISNUMBER(1/Cum),1))),0)),"")

 

[XOR LX]

Hi XOR LX,

Interested to know why you went for a non-array formula, which I see harnesses INDEX, and not something akin to this array version, which has less function calls:

=IF(ROWS(D$2:D2)>SUM(B$2:B$5),"",INDEX(A$2:A$5,MATCH(TRUE,MMULT((ROW(A$2:A$5)>=TRANSPOSE(ROW(A$2:A$5)))+0,B$2:B$5)>=ROWS(D$2:D2),0)))

Was there a reason for your choice?

No - is the answer!

To be honest I often veer more on the side of avoiding this method of inserting an additional INDEX function (actually here an N is required as well) for the sake of circumventing the need for CSE. In fact, I could perhaps be accused of being a touch hypocritical, since I've also made posts to the effect of advising people against this very practice.

Still, perhaps the occasional use is not such a bad thing: unless we're talking a huge number of iterations of that formula, the difference in performance shouldn't be too great between our two versions. And besides, perhaps some people might be interested to know that e.g. a construction containing TRANSPOSE does not necessarily require CSE.

Still, point taken, and I will probably try to avoid such practice from now on. "Avoidance" of array formulas is no doubt something that we should not be promoting; there's enough "fear" and misunderstanding of them as it is.

Cheers,

Matty (Also in Yorkshire!)

Good to know!

 

[bosco_yip]

[table="width: 500, class: grid, align: center"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[/tr]
[tr]
[td]Nigel[/td]
[td]3[/td]
[td][/td]
[td]Nigel[/td]
[/tr]
[tr]
[td]Betty[/td]
[td]2[/td]
[td][/td]
[td]Nigel[/td]
[/tr]
[tr]
[td]Dave[/td]
[td]0[/td]
[td][/td]
[td]Nigel[/td]
[/tr]
[tr]
[td]Bill[/td]
[td]2[/td]
[td][/td]
[td]Betty[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]Betty[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]Bill[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]Bill[/td]
[/tr]
[/table]

Column D is what I'm after. For now.

D2, enter formula and copy down :

=LOOKUP(ROW(A1),SUMIF(OFFSET(B$1,,,ROW($1:$99),),"<>")+1,A$2:A$99)&""

Regards

 

[Matty]

D2, enter formula and copy down :

=LOOKUP(ROW(A1),SUMIF(OFFSET(B$1,,,ROW($1:$99),),"<>")+1,A$2:A$99)&""

Regards

That's clever, bosco_yip!

Matty