Excel expert needed

Sort by newest Sort by date
tsptalk

tsptalk

Moderator
Staff member
Reaction score
1,584
I need some help with an Excel issue.

I have a spreadsheet with two columns, A & B.

I want to combine the columns into a column C and completely remove anything that shows up in both A & B. Not just remove the duplicates, but delete that entry completely.

[TABLE="class: outer_border, width: 200"]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]Bill[/TD]
[TD="align: center"]Bill[/TD]
[/TR]
[TR]
[TD="align: center"]Mary [/TD]
[TD="align: center"]Bob[/TD]
[TD="align: center"]Peggy[/TD]
[/TR]
[TR]
[TD="align: center"]Peggy[/TD]
[TD="align: center"]Mary[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

Any idea? Thank you!
 
Do you want column c to show bob bill?
if so, , put =sign in column c ,like
= a" "&" "b
a is cell a and b is cell b
sorry, we don't have power due to tornado and I am just using my cell phone. I could give better instructions once power is back. Best I can do under this condition.
 
Do you want to delete column a and b where c becomes a or do you want column a and be to remain but only blank?
This is too easy. What am I missing? You don't need an expert for this.
 
I have A & B and I want to create the C column. If a value is in both A & B columns, I don't want them at all in C.

I don't care what happens to column A and B. I will take C and do something with it.

So here I am trying to come up with a list (C) that deletes Bob and Mary because they are in both columns A & B.

062415e.gif


I hope that makes sense.

I see where you are going Maricar19. I'll explore that.

Thanks!
 
tsptalk said:
I need some help with an Excel issue.

I have a spreadsheet with two columns, A & B.

I want to combine the columns into a column C and completely remove anything that shows up in both A & B. Not just remove the duplicates, but delete that entry completely.

[TABLE="class: outer_border, width: 200"]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]Bob
[/TD]
[TD="align: center"]Bill[/TD]
[TD="align: center"]Bill[/TD]
[/TR]
[TR]
[TD="align: center"]Mary [/TD]
[TD="align: center"]Bob[/TD]
[TD="align: center"]Peggy[/TD]
[/TR]
[TR]
[TD="align: center"]Peggy[/TD]
[TD="align: center"]Mary
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

Any idea? Thank you!
Click to expand...

Without using VBA, this can be done quickly, but you'll have to use the excel feature to remove the blank cells afterward:

[TABLE="class: outer_border, width: 200"]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]Bob
[/TD]
[TD="align: center"]Bill[/TD]
[TD="align: center"]Bill[/TD]
[/TR]
[TR]
[TD="align: center"]Mary[/TD]
[TD="align: center"]Bob[/TD]
[TD="align: center"][blank][/TD]
[/TR]
[TR]
[TD="align: center"]Peggy[/TD]
[TD="align: center"]Mary
[/TD]
[TD="align: center"]Peggy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

Remove Empty Cells in Excel 2007 or 2010 Spreadsheets

Post wether that's OK, then I'll generate and post the formulas you need.
 
Blanks are fine. Thanks.

Just to make sure, here's a larger example just dropping Mary, the only duplicate.

062415f.gif
 
tsptalk said:
Not at all. TY!
Click to expand...

no problem.

Final check:

You will get two columns as output, C & D, along with the blanks. You would have to copy and paste (blanks and all) one of those columns (just the data, not the whole column) somewhere underneath the last entry of the other to make one longer column--including blanks. Then you would remove the blanks as discussed.

If this is ok, I can began.

(Forgive all the questions...I find it's best to be very clear with Excel projects :smile:
 
i'm just glad "burrocrat" doesn't show up in either column a or column b at this point, yet.
 
If someone doesn't solve this (don't want to parallel path a solution), PM me and I will check the thread out. I am pretty handy with Excel too. Thanks!!
 
That will work. Thanks!

userque said:
no problem.

Final check:

You will get two columns as output, C & D, along with the blanks. You would have to copy and paste (blanks and all) one of those columns (just the data, not the whole column) somewhere underneath the last entry of the other to make one longer column--including blanks. Then you would remove the blanks as discussed.

If this is ok, I can began.

(Forgive all the questions...I find it's best to be very clear with Excel projects :smile:
Click to expand...
 
ok, it's ready for you to test it out...

just want to tweak it for you if you have headers.?
 
Last edited:
Thanks. Sorry... had to run a minute. Nothing pretty. I just need something to create the list.
 
tsptalk said:
Thanks. Sorry... had to run a minute. Nothing pretty. I just need something to create the list.
Click to expand...

I understand. The headers question was more about how to initialize the formulas for you; and how I explain how to use it. Trust me, there is nothing pretty about this solution :smile: Assuming no headers:

Column C; Row 1:
=IFERROR(IF(MATCH(A1,OFFSET($B$1,0,0,5,1),0),"",A1),A1)

Where "5" refers to the LAST ROW WITH DATA IN COLUMNS A/B

Column D; Row 1:
=IFERROR(IF(MATCH(B1,OFFSET($A$1,0,0,5,1),0),"",B1),B1)

Where "5" refers to the LAST ROW WITH DATA IN COLUMNS A/B

Fill these formulas down to the last row with data in columns a/b

They should populate with names and blanks.

COPY AS VALUES ONLY columns c and d into columns e and f. This will remove the formulas and leave only values/blanks. Then proceed as we discussed.

Let me know if you need more information on copying as values only.

EDIT: Instead of "5" above, use YOUR value. "5" is just an example.
 
OK, I must have done something wrong because I end up with a concatenation of the two columns in the final column (I'll call "G").

2 columns, A & B.

C & D filled with new formulas using final row of column as the "5"

Copy values of C & D to E & F

Concatenate F to bottom of E to create G.

Sort column G removing 0's.

Result is all email addresses in one row.

Do you have a small excel file example you can email or PM (or attach here)?

Thanks
 
tsptalk said:
OK, I must have done something wrong because I end up with a concatenation of the two columns in the final column (I'll call "G").

2 columns, A & B.

C & D filled with new formulas using final row of column as the "5"

Copy values of C & D to E & F

Concatenate F to bottom of E to create G.

Sort column G removing 0's.

Result is all email addresses in one row.

Do you have a small excel file example you can email or PM (or attach here)?

Thanks
Click to expand...

Sure, I'll send it to you. But, if you COPY the data (not whole column) from F to bottom of E. Your column to work with is now E. I'm not sure how G entered the picture or exactly what you mean by concatenate as you are using it.?

Stand by for example sheet.
 
Tom, while I wait for your PM:

excel.png

E and F were copied by value from C and D.

F (data, not column) copied to bottom of E. E is twice as long now.

E is the final column to remove blanks and sort if you like. I darkened some cells for clarity.
 
Last edited:
You must log in or register to reply here.
Back
Top