duplicate rows based on cell value...

[Urraco]

Hello Mr Excel

many times I have to solve this type of list
I need to multiply each cell from column A based based on the number in column B, then add count number from each unique name
how to do this job faster instead of doing it manually?
thank you very much

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	no	name	...to duplicate	…to count	>>>	no	name	duplicate	count
2	1	José Luis	2			1	José Luis	2	1
3	2	Juan	5			1	José Luis	2	2
4	3	Miguel Ángel	3			2	Juan	5	1
5	4	José	1			2	Juan	5	2
6	5	Francisco	7			2	Juan	5	3
7	6	Jesús	3			2	Juan	5	4
8	7	Antonio	4			2	Juan	5	5
9	8	Alejandro	2			3	Miguel Ángel	3	1
10	9	Pedro	3			3	Miguel Ángel	3	2
11	10	Juan Carlos	4			3	Miguel Ángel	3	3
12	11	…				4	José	1	1
13						5	Francisco	7	1
14						5	Francisco	7	2
15						5	Francisco	7	3
16						5	Francisco	7	4
17						5	Francisco	7	5
18						5	Francisco	7	6
19						5	Francisco	7	7
20						6	Jesús	3	1
21						6	Jesús	3	2
22						6	Jesús	3	3
23						7	Antonio	4	1
24						7	Antonio	4	2
25						7	Antonio	4	3
26						7	Antonio	4	4
27						8	Alejandro	2	1
28						8	Alejandro	2	2
29						9	Pedro	3	1
30						9	Pedro	3	2
31						9	Pedro	3	3
32						10	Juan Carlos	4	1
33						10	Juan Carlos	4	2
34						10	Juan Carlos	4	3
35						10	Juan Carlos	4	4
36						11	…
Sheet1

Cell Formulas
Range	Formula
I2:I35	I2	=COUNTIFS($G$2:G2,G2)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	no	name	...to duplicate	…to count	>>>	no	name	duplicate	count
2	1	José Luis	2	1		1	José Luis	2	1
3	2	Juan	5	3		1	José Luis	2	2
4	3	Miguel Ángel	3	8		2	Juan	5	1
5	4	José	1	11		2	Juan	5	2
6	5	Francisco	7	12		2	Juan	5	3
7	6	Jesús	3	19		2	Juan	5	4
8	7	Antonio	4	22		2	Juan	5	5
9	8	Alejandro	2	26		3	Miguel Ángel	3	1
10	9	Pedro	3	28		3	Miguel Ángel	3	2
11	10	Juan Carlos	4	31		3	Miguel Ángel	3	3
12	11	…		35		4	José	1	1
13						5	Francisco	7	1
14						5	Francisco	7	2
15						5	Francisco	7	3
16						5	Francisco	7	4
17						5	Francisco	7	5
18						5	Francisco	7	6
19						5	Francisco	7	7
20						6	Jesús	3	1
21						6	Jesús	3	2
22						6	Jesús	3	3
23						7	Antonio	4	1
24						7	Antonio	4	2
25						7	Antonio	4	3
26						7	Antonio	4	4
27						8	Alejandro	2	1
28						8	Alejandro	2	2
29						9	Pedro	3	1
30						9	Pedro	3	2
31						9	Pedro	3	3
32						10	Juan Carlos	4	1
33						10	Juan Carlos	4	2
34						10	Juan Carlos	4	3
35						10	Juan Carlos	4	4
36
37
38
39
Sheet4

Cell Formulas
Range	Formula
F2:H38	F2	=IF(ROWS($F$2:$F2)>=MAX($D$2:$D$100),"",INDEX(A$2:A$100,MATCH(ROWS($F$2:$F2),$D$2:$D$100,1)))
I2:I38	I2	=IF(G2="","",COUNTIFS($G$2:G2,G2))
D3:D12	D3	=C2+D2

 

[Urraco]

thanks, Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Urraco]

You're welcome & thanks for the feedback.

I apologize for coming back, but I didn't notice at first..
if I only have one row, this formulas is no longer works...
can it work in case I only have one row?
thank you

Book2
	A	B	C	D	E	F	G	H	I
1	no	name	to duplicate	to count		no	name	duplicate	count
2	1	Juan	9
3
Sheet1

Cell Formulas
Range	Formula
A2	A2	=IF(B2="","","1")
F2:H3	F2	=IF(ROWS($F$2:$F2)>=MAX($D$2:$D$100),"",INDEX(A$2:A$100,MATCH(ROWS($F$2:$F2),$D$2:$D$100,1)))
I2:I3	I2	=IF(G2="","",COUNTIFS($G$2:G2,G2))

 

[Fluff]

You need to add the running sum to col D like

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	no	name	to duplicate	to count		no	name	duplicate	count
2	1	Juan	9	1		1	Juan	9	1
3				10		1	Juan	9	2
4						1	Juan	9	3
5						1	Juan	9	4
6						1	Juan	9	5
7						1	Juan	9	6
8						1	Juan	9	7
9						1	Juan	9	8
10						1	Juan	9	9
11
12
Sheet4

Cell Formulas
Range	Formula
A2	A2	=IF(B2="","","1")
F2:H12	F2	=IF(ROWS($F$2:$F2)>=MAX($D$2:$D$100),"",INDEX(A$2:A$100,MATCH(ROWS($F$2:$F2),$D$2:$D$100,1)))
I2:I12	I2	=IF(G2="","",COUNTIFS($G$2:G2,G2))
D3	D3	=D2+C2