Suppose I’ve got three columns of text.
I want it to go through the first column and find out if two consecutive cells (A8 and A9, for example) are the same.
If they are, I want it to check if B8 and B9 are the same.
If they are too, I want it to combine C8 and C9 (so that C8 is composed of the text of C8 followed by the text of C9) and then delete row 9.
Is that more than it can do?
Try this.
=if(a8=a9,if(b8=b9,concatenate(c8,c9),""),"")
But as for deleting row 9, I think you will have to write yourself a macro.
My macro skills are limited, but I have gotten an awful long way by recording keystrokes and some dodgy editing. It may be easier in this case to copy rows 10+ to over-write row 9.
Hope this helps.
Not sure exactly what you want but, maybe the following would help
In Column D, Row 3, add the following formula:
=IF(AND(A3=A4,B3=B4),CONCATENATE(C3,C4),IF(AND(A3=A2,B3=B2),"",C3))
and drag it down to the end of the column.
You should see the following
A B C D
x w abc abc
s q def def
s e ghi ghi
e e jkl jkl
r r mno mno
t q pqr pqrstu
t q stu
gg o vwx vwx
q e yza yza
w w bcd bcd
gg s efg efg
k q hij hij
m x klm klmnop
m x nop
a t qrs qrs
b t tuv tuv
Now, select columns A-D and Select “Data”->“Filter”->“AutoFilter”
Go to the drop-down menu of column D and select “(Nonblanks)”
This should look something like
A B C D
x w abc abc
s q def def
s e ghi ghi
e e jkl jkl
r r mno mno
t q pqr pqrstu
gg o vwx vwx
q e yza yza
w w bcd bcd
gg s efg efg
k q hij hij
m x klm klmnop
a t qrs qrs
b t tuv tuv
You can copy column D, which is what I assume you want
To add a smidgen to Polerious’ fine post, I suggest you use Data | Filter a little differently.
After creating the formulas in column D, select all of column D, Copy, then choose Paste Special… and choose Values. That’ll convert the formulas into the plain text of their values.
Then select all the rows of your table and columns A thrrough D, then choose Data | Filter | Autofilter. In column D, choose (Blanks). Then select and delete all the rows below the top row where the filter dropdowns are. That will delete all the rows where D is blank, but leave all the other (currently invisible) rows intact.
Then go back to the filter dropdown for column D and choose (All).
Viola. All your duplicate rows are gone and your column D comtains the concatenation of the dupe rows.
You didn’t tell us how you want to handle 3 or more rows with duplicate column A & B. Polerious’s formula won’t aggregate them all, but a few minutes of diddling with variations on his formula should do it for you.