consolidate rows based on ID

[JoeHardy]

Hi, all. I'm having some difficulty cracking a problem I've encountered. I have a large data set that I have pulled into Excel from a web scraper. I'm looking to consolidate rows but I can't seem to find a command or formula that gives me quite what I want. Here's an example of the situation:

ID	Content	Author
ID0001	Content written by Author #1
ID0001		Author #1
ID0002	Content written by Author #2
ID0002		Author #2
ID0003		Author #3
ID0003	Content written by Author #3
ID0004		Author #4
ID0004
ID0004	Content written by Author #4

I can get rid of the rows where there are no authors or content (Filter and delete), but then I want to consolidate the rows that have the same ID value, so that the content written and the author appear on the same row. The trouble is that the content and the author are not always misaligned in the same direction. Sometimes the author row comes first and sometimes the content row comes first.

I thought about trying to write a formula that compares ID values across cells and copies the values from the "Content" or "Author" column but I can't wrap my head around how that would look beyond comparing the cells in the ID column and returning a value for true or false.

Any options that you think I should investigate further?

[CRIMEDOG]

you could first delete the rows with no content written by, or author
then Sort : by ID - then Content written by
This will make it look

A2; ID001 B2: Content Written by ... C2; Blank
A3: ID001 B3: Blank C3: Author #1


add new colulumn D with =if(Bx<>"",Cx+1,"") where X is current row and X+1 is the next row number ( B2<>"",C3,"" )
this will bring the Author up to the row Content written by....

Then you can copy and paste as value - this will remove formulas - then you can sort on column B (this will group) then delete the rows with Blank column B

[JoeHardy]

Brilliant! That works perfectly. It hadn't occurred to me to nest a couple of sort functions first, but once I did, the formula makes perfect sense.

[JLGWhiz]

If there are only two rows per ID

Please Login or Register  to view this content.

[FlameRetired]

Another way.

1. Apply Filters.
2. In "Content" select Blanks.
3. Select the remaining IDs and delete them.
4. Clear the filter.
5. Select column A2:C10
6. Press Ctrl + G (or the F5 function key).
7. Click Special.
8. Select Blanks.
9. Click OK
10. Press Ctrl plus the "-" on the numeric keypad. (keyboard short cut for delete cells)
11. Select Shift cells up.

It takes longer to read than it does to do it.