Counting up how many streams are flowing into it

[LostinExcel80]

I have inherited a set a data that is laid out as followed and I am wanting to count the cells to the right and down until they hit another cell in the same column a lot will be 0 as they will have no streams into them. While long term maybe to redo that data into a better layout this is what I have and need to work with for now. Each stream in data 1-7 will always only have one line filled, but a unique name...

DATA 1	DATA 2	DATA 3	DATA 4	DATA 5	DATA 6	DATA 7	number of streams under stream. (I need this row formula for dynamic as streams are added and removed)	---->alot more data not needed for this example but is relevant to the specific stream
Final stream A							2
	Second to final stream A						1
		Third to final stream A					0
Final Stream B							5
	Second to Final stream B A						0
	Second to Final stream B B						0
	Second to Final stream B C						0
	Second to Final stream B D						1
		Third to final stream B D A					0

For this example, if there is a value in cell A1, count all cells to the right and down that contain data UNTIL it hits a value in A again.
I am pulling hair trying to figure this out.

The logic is this as follows but I can't figure that out in excel

DATA 7 should always count as 0
DATA 6 should count all DATA 7 directly under it until DATA 6 or DATA 5 or DATA 4 or DATA 3 or DATA 2 or DATA 1 is not blank,
DATA 5 should count all DATA 6 and DATA 7 directly under it until DATA 5 or DATA 4 or DATA 3 or DATA 2 or DATA 1 is not blank,
DATA 4 should count all DATA 5, DATA 6 and DATA 7 directly under it until DATA 4 or DATA 3 or DATA 2 or DATA 1 is not blank,
DATA 3 should count all DATA 4, DATA 5, DATA 6 and DATA 7 directly under it until DATA 3 or DATA 2 or DATA 1 is not blank,
DATA 2 should count all DATA 3, DATA 4, DATA 5, DATA 6 and DATA 7 directly under it until DATA 2 or DATA 1 is not blank,
DATA 1 should count all DATA 2, DATA 3, DATA 4, DATA 5, DATA 6 and DATA 7 directly under it until DATA 1 is not blank

Please help thank you...

 

[Peter_SSs]

Welcome to the MrExcel board!

Does your excel 365 have the FILTER function?
Could we use a helper column (which could be hidden?

If "yes" to both, try this. Note that the Formulas (at least the helper column need to be copied down at least one row further than the data.

20 04 11.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATA 1	DATA 2	DATA 3	DATA 4	DATA 5	DATA 6	DATA 7	number
2	Final stream A							10		1|Final stream A
3		Second to final stream A						9		2|Second to final stream A
4			Third to final stream A					8		3|Third to final stream A
5				xx				7		4|xx
6					xx			5		5|xx
7						xx		2		6|xx
8							xx	0		7|xx
9							xx	0		7|xx
10						xx		0		6|xx
11						xx		0		6|xx
12					xx			0		5|xx
13	Final Stream B							5		1|Final Stream B
14		Second to Final stream B A						0		2|Second to Final stream B A
15		Second to Final stream B B						0		2|Second to Final stream B B
16		Second to Final stream B C						0		2|Second to Final stream B C
17		Second to Final stream B D						1		2|Second to Final stream B D
18			Third to final stream B D A					0		3|Third to final stream B D A
19										1
20										1
Count (2)

Cell Formulas
Range	Formula
H2:H19	H2	=IF(J2=1,"",MATCH(INDEX(FILTER(J3:J$20,LEFT(J3:J$20,1)<=LEFT(J2,1)),1)&"",J3:J$20&"",0)-1)
H20	H20	=IF(J20=1,"",MATCH(INDEX(FILTER(J$20:J21,LEFT(J$20:J21,1)<=LEFT(J20,1)),1)&"",J$20:J21&"",0)-1)
J2:J20	J2	=IFERROR(MATCH("?*",A2:G2,0)&"|"&CONCAT(A2:G2),1)

If this is not satisfactory for some reason, post back with details and also advise if a vba solution would be acceptable.

 

[LostinExcel80]

VBA not so much at the moment,

This is what I need, thank you so much!!!
Tho my data is messed up on rows where the streams are numbers, it works perfect with the letters, but if the name of the stream is just a number (which a lot are) it doesn't work even with formatting changed to text

 

[Peter_SSs]

but if the name of the stream is just a number (which a lot are) it doesn't work

In a way I am glad that happened because I had some errors in my previous post and also some methods that were not efficient or not required.
I think this is much better/simpler as well as working with text or numbers.

20 04 11.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATA 1	DATA 2	DATA 3	DATA 4	DATA 5	DATA 6	DATA 7	number
2	Final stream A							10		1
3		Second to final stream A						9		2
4			Third to final stream A					8		3
5				1				7		4
6					2			5		5
7						3		2		6
8							4	0		7
9							5	0		7
10						6		0		6
11						7		0		6
12					8			0		5
13	Final Stream B							5		1
14		123456789						0		2
15		Second to Final stream B B						0		2
16		Second to Final stream B C						0		2
17		Second to Final stream B D						1		2
18			Third to final stream B D A					0		3
19										0
20										0
Count (3)

Cell Formulas
Range	Formula
H2:H20	H2	=IF(J2=0,"",MATCH(INDEX(FILTER(J3:J$21,J3:J$21<=J2),1),J3:J$21,0)-1)
J2:J20	J2	=IFERROR(FILTER(SEQUENCE(,COLUMNS(A2:G2)),A2:G2<>""),0)

 

[LostinExcel80]

With the H2 formula is there a way to have a dynamic end instead of J$21 so it can grow as the column grows?

 

[Peter_SSs]

With the H2 formula is there a way to have a dynamic end instead of J$21 so it can grow as the column grows?

Copy the column J formula down further than you will ever need (row 100? row 2000?)
Then adjust the H2 formula by changing the J$21 references to the next cell below where you copied the column J formulas to (eg J$101 or J$2001)
Copy this new H2 formula down as far as you want (limit of H100, H2000 etc)

 

[LostinExcel80]

That still static numbers but not a big deal for now thank you for all the help.

It works on my computer but not others
getting error function is not valid. Is that just an update issue?

 

[Peter_SSs]

It works on my computer but not others

FILTER and SEQUENCE functions are only available in Excel 365 (& not even everybody's version of that until later in the year). Could that be the problem?

That still static numbers

To address both of the above issues, you could try this. If it isn't already, turn the whole data & helper column into a formal table (Insert ribbon tab) then try these formulas.
You will need to use the table name in your sheet where my formulas use Table13

LostinExcel80 2020-04-15 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATA 1	DATA 2	DATA 3	DATA 4	DATA 5	DATA 6	DATA 7	number	helper
2	Final stream A							10	1
3		Second to final stream A						9	2
4			Third to final stream A					8	3
5				1				7	4
6					2			5	5
7						3		2	6
8							4	0	7
9							5	0	7
10						6		0	6
11						7		0	6
12					8			0	5
13	Final Stream B							5	1
14		123456789						0	2
15		Second to Final stream B B						0	2
16		Second to Final stream B C						0	2
17		Second to Final stream B D						1	2
18			Third to final stream B D A					0	3
19
Count (4)

Cell Formulas
Range	Formula
H2:H18	H2	=IFERROR(MATCH(TRUE,INDEX([helper],ROW()-ROW(Table13[[#Headers],[helper]])+1):INDEX([helper],ROWS([helper]))<=[@helper],0)-1,MAX(ROW([helper]))-ROW())
I2:I18	I2	=IFERROR(MATCH(TRUE,INDEX(Table13[@[DATA 1]:[DATA 7]]<>"",0),0),0)

 

[LostinExcel80]

This is perfect thank you.

Now since others will not have the updated excel which I found out today, can we make a macro to get this to work is it simple?

 

[LostinExcel80]

I have a vba but it is extremely slow, it uses the helper row too.

Sub Count()

Dim StreamCount, x, y As Integer
Dim HowManyRows As Long

HowManyRows = Sheets("sheetname").Cells(Rows.Count, "I").End(xlUp).Row

For x = 2 To TotalRows
StreamCount = 0

If Cells(x, 9) <> "" Then
    For y = 1 To TotalRows - x
         If Cells(x, 9) < Cells(x + y, 9) Then
              TribCounter = TribCounter + 1
         ElseIf Cells(x, 9) >= Cells(x + y, 9) Or Cells(x + y, 9) <> "" Then
              Cells(a, 8) = StreamCount
              GoTo NextStream
        End If
    Next y
End If


NextStream:
Next x
End Sub