Group sort needed - have burned the candle at both ends!

[NaomiNaomi]

I have a spreadsheet with 13,000 rows. The rows come in groups: 3 or 4 rows have the same case number - there are about unique 4000 cases. The next column over has ONE additional number for only ONE of those rows - the rest of the cells are blank for all the other matching case number rows. I would do a normal sort, but for the fact that MOST of those cases do not have that additional number, and must be scrapped. I need to do a group sort based on that one additional piece of information, and then keep the case numbers together. Unfortunately, I can't upload a sample. I think it's disabled here at work. will the table below post correctly, I wonder...

Case # 1017654 has the matching MIS ID: 605635. How can I sort all the 1017654 rows to stay grouped with the 605635 row, so that all the rows with case numbers and NO MISID move to the bottom where they'll be deleted?

Thanks.

[table="width: 500, class: grid, align: left"]
casenum MIS id suffix line action_code action_dt seq_no
10124 01 01 168L 08/20/2012 10
10124 01 01 169I 09/05/2012 10
10124 01 01 169D 09/05/2012 5
101499 01 01 168L 07/12/2012 10
101499 01 01 169D 09/24/2012 50
101499 01 01 169I 09/24/2012 55
101499 01 01 168L 09/24/2012 10
1017654 01 01 168L 08/29/2012 15
1017654 01 01 169D 09/11/2012 5
1017654 01 01 169I 09/13/2012 10
1017654 605635 01 01 169I 9/13/2012 10
1019483 01 01 169I 07/11/2012 20
1019483 01 01 169D 07/11/2012 15

[ChemistB]

Okay, since you can't upload;
Assuming Case ID in Column A
Is 605636, the only value (in your example) in Col B?
Why don't you just sort on Col B, bringing all the blanks together and delete them?

[NaomiNaomi]

Because I need some of those blank rows.

I need them wherever they contain the same case as the row that has the extra value.

I was able to upload the example after all: I've highlighted about 3 cases. Cases that I want to save are in light yellow, and all have identical case numbers, with one that has an MIS_ID number. Scroll down to find them. All the other rows (no color) contain cases that I want to dump, because they have no matching MIS_ID.

Thanks!

[ChemistB]

Here's a way, In H2 dragged down

=IF(SUMIF(A2:$A$102,A2,B2:$B$102)>0,A2,"")

Then select all data including H and filter on Blanks
Delete all visible rows

FYI: Rows 86:88 should not be yellow, they are a different number.

[NaomiNaomi]

By God, I think you've got it!!

[NaomiNaomi]

Chemist, I am waiting with bated breath to see if this solution fully works for me...I think it will. (our system is unbelievably slow) In the meantime, thank you!

[ChemistB]

Glad to hear it's working.

[NaomiNaomi]

Now I have a similar request, only the data is in a different order. I need all blank cells completed for the same case numbers. Only one of the cells has the complete information. Please see the attached.

[ChemistB]

In A2

=LOOKUP(B2,$B$2:$B$15,$A$2:$A$15)
Then copy and paste that formula into all other blank cells.

[NaomiNaomi]

The lookup worked for the sample, but not when I added different values below, please see the attached.

[ChemistB]

In A2

=INDEX(A3:$A$26,COUNTIF($B2:$B$26,B2)-1), copied into all blank cells

[NaomiNaomi]

Beautiful!

Thanks!!

Naomi

[ChemistB]

It worked originally because each number was greater than the last. I need to re-evaluate.

[Cutter]

@ NaomiNaomi

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[NaomiNaomi]

Thanks for finishing this off for me, and I had already starred chemist B twice!

[ChemistB]

LOL, thank you NaomiNaomi