Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

if i have a workbook filled with data like this example below here

   A      B      C
1 test   edge   blue
2 test   edge   red
3 dust   daf    grey
4 dust   daf    red

And i want to clean this up so it says

   A      B      C     D
1 test   edge   blue  red
2 dust   daf    grey  red

anyone can help how to do this ?

share|improve this question
    
Do you have access to Unix, Linux, a Unix-like system, Cygwin, or some other Unix look-alike? If you do, the easiest solution might be to export your data to a CSV file, process it in awk, and then re-import it to Excel. –  Scott Dec 18 '12 at 20:40
    
If in your example the fourth column were say w, x, y ,z what would be the result you want? –  pnuts Dec 19 '12 at 0:26
add comment

1 Answer

Sort by A then B, then put into D1:

=IF(AND(A1=A2,B1=B2),C2,"")

Fill Down column D, then either copy and paste as values or filter out the blanks in column D. That should work if your data is all like the above.

share|improve this answer
    
it have to be dynamic, 10 fields cold be the same in A anb B i dont know what the data will be before i get the sheet. –  Darkmage Dec 18 '12 at 14:02
    
Well, in that case add more columns: E1 will have =IF(AND(A1=A3,B1=B3),C3,""), and so on. –  benshepherd Dec 18 '12 at 14:05
    
i cant do this manual the sheet might have 10000 lines, and hundred of collums, i need a macro or somthing. this must be a problem that has been solved before. –  Darkmage Dec 18 '12 at 14:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.