Count rows and display the results

[WT2008]

Please see the attached picture for details of what I'm trying to accomplish.

I have a string of numbers in A. Each number is separated by commas. Going down the column I would like to check the skipped rows of each number until it shows up again in the column.

For example Column B on row 6 shows 1 - 2 - x - 4 - x

# 02 skipped one row from row 4 to row 6 so the result on B should be (1)
# 05 appeared on row 3 then skipped 2 rows and it is again in row 6, so skipped (2)
# 22 and 33 are not on previous rows so it is first time numbers (x)
# 25 was on row 1 and skipped 4 rows before showing up on row 6 so skipped (4).

I can separate the string into columns of that option will be easier.

Any help is appreciated.

excel_skip.jpg

[FlameRetired]

WT please upload a sample Excel workbook (not pics or screenshots ... it saves us having to retype data you already have.)

If you are not familiar with how to do this:

To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[WT2008]

Thank you FlameRetired for your message. Here is the excel workbook.
Regards

[FlameRetired]

WT there are versions of Excel with features worth exploring, but not if they do not apply. For ex. Flash Fill.

That said please update your profile to let us know what version(s) of Excel you have.

[WT2008]

Office 2010. Thank you.

[FlameRetired]

Thank you WT.

I couldn't come up with any formula approach that didn't require helper columns.

This formula in B2 filled down and across does that. It parses the original strings.

Please Login or Register  to view this content.

In the calculated section "x - " is the default for H2:L2. Starting in H3 filled down and across.

Please Login or Register  to view this content.

Then the final concatenated output in column N

Please Login or Register  to view this content.

[FlameRetired]

I noticed a possible error in my previous formula in H3:L17. It calculates the difference between the current number row and the first occurrence of that number. This formula calculates the difference between the current row and the most recent previous (last) occurrence. I suspect this is what you want?

Please Login or Register  to view this content.

[WT2008]

Yes the second file it much better.
I still found some miscalculations in some columns but like I said, much better.
Here is the file with the errors.
Thank you.

[FlameRetired]

Array enter this formula in H3 fill down and across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

There were some repeating spaces in column P. Once cleaned up the only FALSE is in row 11. I believe my formula is correct. Can you double check L11?

[Phuocam]

Or try in H2:

=IFERROR(ROW()-LARGE(IF($B$1:$F1=B2,ROW($B$1:$F1)),1)-1,"x")&IF(COLUMNS($H1:H1)<5," - ","")

Enter with Ctrl+Shift+Enter.

[FlameRetired]

@Phuocam
Nice one. Very compact.

[FlameRetired]

Phuocam you are an inspiration.

Try this non array in H2.

Please Login or Register  to view this content.

[WT2008]

@FlameRetired your last two codes worked like a charm. Thank you!

I noticed the extra space on P12 so once I fixed that it changed to TRUE. Also I made a mistake on P11, it should be 7 - 1 - 5 - 5 - 2 and it is also TRUE.

@Phuocam Thank you, it also worked like a charm.

Very happy receiving great help here from nice guys.

[FlameRetired]

You are welcome. Thank you for the kind words and follow-up re: typos.