CODEWITHSUNDEEP
excelcsvexcel-formula
Joe
Joe

Reputation: 512

Merge Rows in Excel

A               B   C       D       E   F   G   H
ACC'L SCIENCE   0.5 TRUE    557092  87  87      
ACC'L SCIENCE   0.5 TRUE    557092          88  88
ART             0.5 TRUE    557092  98  98      
ENGLISH 6       0.5 TRUE    557092  88  88      
ENGLISH 6       0.5 TRUE    557092          87  87
GIRLS PE        0.5 TRUE    557092  100 100     
GIRLS PE        0.5 TRUE    557092          94  94

I have the above 7 rowsin an excel file. I'm trying to check if consecutive columns have the same value, they should be merged into one row. The formula I'm attempting would create the following data

A               B   C       D       E   F   G   H
ACC'L SCIENCE   0.5 TRUE    557092  87  87  88  88  
ART             0.5 TRUE    557092  98  98      
ENGLISH 6       0.5 TRUE    557092  88  88  87  87  
GIRLS PE        0.5 TRUE    557092  100 100 94  94

Pseudo code would look something like :

=IF(A1 = A2),--> G1,H1 = G2, H2

I have to then also account for line skips -- like the art class. Would using an 'IF' statement like this to check constraints be an efficient method here ?

The problem with IF seems to be that it returns a Boolean when I really just want it to merge. Is there a sleek non-VBA way to do this ? I've found some good VBA options with similar data -- but seems there would be a way not to have to break it out. I know of MATCH and INDEX, but haven't come up with a way to just merge them out.

I tried an array MATCH - INDEX formula but it was returning odd values -- I inserted a new column so as not to mess with the data --Tried to basically merge the numbers into a single row. 

MATCH($A$2:$A$7, INDEX(E2 & F2, $G$2:$G$7 & $H$2:$H$7))

I'm pretty new to excel, though I've done some python. Any pointer in the right direction, I would appreciate.

Upvotes: 1

Views: 363

Answers (2)

John Bustos
John Bustos

Reputation: 19544

I think there are probably easier ways to do this than this, but my first idea would be a 2-step process:

In cell I1, say, put in the following formula:

=IFERROR(INDEX(A$1:A$7,MATCH(1,(--($A$1:$A$7=$A1))*(--(A$1:A$7<>"")),0)),"")

and enter it as an array formula by pressing ctrl + shift + enter

You can then drag this down and across as needed.

(This formula is basically saying that so long as the values in column A match, to pull in the first non-empty row's value along with it.)

Now, you can Copy, Paste Special > Values and then use Data > Remove Duplicates to remove the duplicate rows.

Like I said, t's probably not THE most elegant solution, but it should do the trick!!

Hope that helps!!

UPDATE:

Given the comment of needing BOTH columns A and D to match, try this formula:

=IFERROR(INDEX(A$1:A$7,MATCH(1,(--($A$1:$A$7=$A1))*(--($D$1:$D$7=$D1))*(--(A$1:A$7<>"")),0)),"")

Upvotes: 1

pnuts
pnuts

Reputation: 59440

If sorted by D and then A, Subtotal with a little fiddling may suit. Use Sum on E, F, G and H for each change in A. Filter and select A Contains... Total. Fill first visible non-header row with =Bx in B where x is one less than the current row number. Copy across to D and down to suit. Unfilter, select all Copy, Paste Special Values over the top. Select A Does Not Contain... Total and delete all visible except header. Replace Total in ColumnA with nothing.

Upvotes: 1

Related Questions