Counting in a column (this is a tough one)

[klin0503]

So I have an annoying dataset to work with. The first column contains ID#, string, string, string, ID#, string, string, string, string, ID#, string, and so on. It is basically a list of patient numbers with a list of their appointments listed underneath them. I need to find a way to count up the number of strings for each patient, like shown below:

Patient#	Count
101	2
Had an appointment
Had an appointment
Deleted-Had an appointment
202	1
Had an appointment
303	1
Deleted - Had an appointment
Had an appointment

The format isn't too important. The data I have is basically column A, and I want to create column B. Any help would be greatly appreciated!

EDIT: Sorry for cross-posting ( http://www.mrexcel.com/forum/excel-q...tough-one.html ) There is a different solution (not fully solved yet though) in that thread if you're interested.

[zbor]

If you end with number in A column (any number, 1234) then use this in B column:

Please Login or Register  to view this content.

confirmed with ctrl+shift+enter (not just enter)

[klin0503]

Ooops sorry I did something wrong. I will try it again

[zbor]

Look at the example

[Richard Buttrey]

Hi,

And are the strings either always 'Had an appointment' or 'Deleted - Had an appointment'. Or do the strings vary?

[XOR LX]

Cross-post:

http://www.mrexcel.com/forum/excel-q...tough-one.html

[klin0503]

They actually don't say either of those. They're varying strings, but i need to exclude the ones with "deleted" in it

[klin0503]

Thanks, the problem now seems to be getting that last patient to work without having to add a number at the end.

[klin0503]

If you end with number in A column (any number, 1234) then use this in B column:

Please Login or Register  to view this content.

confirmed with ctrl+shift+enter (not just enter)

Any way to make it match the next number OR the next blank? (which would solve the problem with the last patient I think)

[zbor]

Yes

Please Login or Register  to view this content.

comfirmed with ctrl+shift+enter

[Richard Buttrey]

@XOR - thanks for noting this

@klin0503 - Please note

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[klin0503]

Yes

Please Login or Register  to view this content.

comfirmed with ctrl+shift+enter

Thanks. This is working on the test file you sent me, but for some reason it's not working on my data set. Could you explain what the 1+ is doing?

edit: It's working now (I made a little mistake in my formula), but I would still like to know what the 1+ is doing.

[klin0503]

@XOR - thanks for noting this

@klin0503 - Please note

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Sorry about this! Won't happen again.