Add to the count if the cell contains a specific name...

[copperwasher]

Hello Excel Gurus,

Over the past couple of hours, I have made some feeble attempts at this.

I need to display a rolling count (column 2), but the count is relative to a specific name (column 1).

Column 1 is typed.
Column 2 is the desired output.

Column 1, will grow in length, as more data is added, so column 2 needs to be able to copy down also

COMPANY NAME (column 1)	COMPANY NAME and COUNT (Column 2)
company B	company B 101
company A	company A 101
company A	company A 102
company A	company A 103
company C	company C 101
company C	company C 102
company B	company B 102
company A	company A 104
company A	company A 105

Any assistance is very much appreciated,

My regards
Gary

 

[RoryA]

Something like:

=A2&" "&COUNTIF(A$2:A2,A2)+100

perhaps?

 

[JvdV]

Book1
	A	B
1	Name	Count
2	company B	company B 101
3	company A	company A 101
4	company A	company A 102
5	company A	company A 103
6	company C	company C 101
7	company C	company C 102
8	company B	company B 102
9	company A	company A 104
10	company A	company A 105
Sheet7

Cell Formulas
Range	Formula
B2:B10	B2	=MAP(SEQUENCE(COUNTA(A:A)-1)+1,LAMBDA(x,INDEX(A:A,x)&" "&COUNTIF(A2:INDEX(A:A,x),INDEX(A:A,x))+100))
Dynamic array formulas.

 

[copperwasher]

Book1
	A	B
1	Name	Count
2	company B	company B 101
3	company A	company A 101
4	company A	company A 102
5	company A	company A 103
6	company C	company C 101
7	company C	company C 102
8	company B	company B 102
9	company A	company A 104
10	company A	company A 105
Sheet7

Cell Formulas
Range	Formula
B2:B10	B2	=MAP(SEQUENCE(COUNTA(A:A)-1)+1,LAMBDA(x,INDEX(A:A,x)&" "&COUNTIF(A2:INDEX(A:A,x),INDEX(A:A,x))+100))
Dynamic array formulas.

That works perfectly JvdV, and well beyond my current comprehension of excel...

5 stars *****

 

[copperwasher]

That works perfectly JvdV, and well beyond my current comprehension of excel...

5 stars *****

Ah....
Sorry about this JvdV,

What if column A has a blank cell, column B cell = 100

Is there a way to keep column B cell blank if the adjacent column A cell is blank ??

COMPANY NAME (column 1)	COMPANY NAME and COUNT (Column 2)
company B	company B 101
company A	company A 101
company A	company A 102
company A	company A 103
company C	company C 101
company B	company B 102

Any guidance is much appreciated.

Btw, I've started to pick through your equation, to understand its methodology, it is certainly pushing my learning curve....

 

[copperwasher]

Oh.... there seems to be a spill error when I extend the equation beyond row 15

Name	Count
company B	company B 101
company A	company A 101
company A	company A 102
company A	company A 103
company C	company C 101
company C	company C 102
company B	company B 102
company A	company A 104
company A	company A 105
company A	company A 106
company A	company A 107
company A	company A 108
company A	company A 109
	100
	100
	100

Adding at row 15, this happens

Name	Count
company B	#SPILL!​
company A
company A
company A
company C
company C
company B
company A
company A
company A
company A
company A
company A
company B

 

[copperwasher]

Oh.... there seems to be a spill error when I extend the equation beyond row 15

Name	Count
company B	company B 101
company A	company A 101
company A	company A 102
company A	company A 103
company C	company C 101
company C	company C 102
company B	company B 102
company A	company A 104
company A	company A 105
company A	company A 106
company A	company A 107
company A	company A 108
company A	company A 109
	100
	100
	100

Adding at row 15, this happens

Name	Count
company B	#SPILL!​
company A
company A
company A
company C
company C
company B
company A
company A
company A
company A
company A
company A
company B

Solved.... my copy down error....

But the blank column error remains, if it can be resolved....

 

[JvdV]

The spill error means there are values in the way of the to be spilled answers. One of these cells in column B isn't actually empty.

 

[copperwasher]

The spill error means there are values in the way of the to be spilled answers. One of these cells in column B isn't actually empty.

And how do I use this formula, if column A has blank cells??

When there are blank cells in Column A, column B cell produces 100, this needs to be blank

Currently, column A cell is blank,
Column B cell is 100

Name	Count
company B	company B 101
company A	company A 101
company A	company A 102
company A	company A 103
company C	company C 101
	100
company B	company B 102
company A	company A 104
company A	company A 105

Ideally
Column A cell is blank, Column B cell is blank

Name	Count
company B	company B 101
company A	company A 101
company A	company A 102
company A	company A 103
company C	company C 101
company B	company B 102
company A	company A 104
company A	company A 105

Any guidance is very much appreciated

 

[copperwasher]

Solved.... my copy down error....

But the blank column error remains, if it can be resolved....

Hi JvdV

Sorry to ask again...

Column A cells can blank, so the column B cell = 100

Name	Count
company B	company B 101
company A	company A 101
company A	company A 102
company A	company A 103
company C	company C 101
	100
company B	company B 102
company A	company A 104
company A	company A 105

Is there a way that if column A is blank, the relative cell in column B is also blank ?

Name	Count
company B	company B 101
company A	company A 101
company A	company A 102
company A	company A 103
company C	company C 101
	100
company B	company B 102
company A	company A 104
company A	company A 105

Your help is very much appreciated...