CODEWITHSUNDEEP
excelexcel-formula
Jin
Jin

Reputation: 61

Count Duplicates by occurrence

I have 2 columns. In one of the columns i have the text "Multi-Drop" and blanks (col-2).

In the other one i have "First Line" and blanks (col-1)

I would like to count the duplicates from col-2 until First Line from col-1 occurs leaving out the blanks in col-3

This would need to be flexible as i won't be selecting a certain range, but the whole column.

I have already tried to play around with the below and offset as well, but i only could get so far as counting the whole column.

=IF(B2=A1,C1,C1+1)

This is a demo of my data, and the expected result (The space between the 1st line of text and the second are blanks)

As you can see First-Line occurs on the first instances of Multi-Drop,but that is based on another formula.

col-1            col-2          Expected Result  


First Line       Multi-Drop      2



                 Multi-Drop     2
First Line       Multi-Drop     5
                 Multi-Drop     5
                 Multi-Drop     5
                 Multi-Drop     5
                 Multi-Drop     5
First Line       Multi-Drop     4
                 Multi-Drop     4
                 Multi-Drop     4
                 Multi-Drop     4
First Line       Multi-Drop     2
                 Multi-Drop     2

Upvotes: 0

Views: 119

Answers (2)

EEM
EEM

Reputation: 6659

Assuming that data is located at A2:B17 enter this FormulaArray in C2 the copy down till C17:

Formulas Array are entered pressing [Ctrl] + [Shift] + [Enter] simultaneously, you shall see { and } around the formula if entered correctly

= IF( EXACT( B2, "" ), "",
IF( EXACT( A2, "First Line" ),
COUNTIF( INDEX(B:B, ROW() )
:INDEX(B:B, IFERROR( - 1 + ROW() + MATCH( "First Line", A3:A$1048576, 0 ), 1048576 ) ), "Multi-Drop" ),
IF( NOT( EXACT( C1, "" ) ), C1,
INDEX(C:C, MAX( ( ( C1:C$2 <> "" ) * 1 ) * ROW( C1:C$2 ) ) ) ) ) )

enter image description here

Suggest to see the following pages for additional information about the functions used: Excel functions (alphabetical)

IFERROR function INDEX function MATCH function ROW function

Upvotes: 1

padawan_IT
padawan_IT

Reputation: 106

This isn't doing 100% of what you want, but you can take it from there.

For cell C2:

=IF(A2="First Line",C3,IF(A1="",C1,Match("First Line",A2:A500,0)))

That works fine if column B has no empty spots.

The last duplicates wont work as it checks the amount of cells between 2 occurances of "First Line". Since no 2nd "First Line" is coming, it gives you an error.

Is that enough help or can you take it from here? I would post a comment instead but I don't have enough points yet, so I can only post answers.

This is a snippet of what I tried

Edit: Forgot that English Excel uses , instead of ;

Fixed that in my code.

Upvotes: 1

Related Questions