Excel expert needed

[tsptalk]

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.



A

B

C



Bob
Bill
Bill


Mary
Bob
Peggy


Peggy
Mary









Any idea? Thank you!

[Maricar19]

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.

[jpcavin]

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.

[tsptalk]

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.

http://www.tsptalk.com/images/mb/062415e.gif

I hope that makes sense.

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

Thanks!

[userque]

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.



A

B

C



Bob

Bill
Bill


Mary
Bob
Peggy


Peggy
Mary










Any idea? Thank you!

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



A

B

C



Bob

Bill
Bill


Mary
Bob
[blank]


Peggy
Mary

Peggy








Remove Empty Cells in Excel 2007 or 2010 Spreadsheets (http://www.groovypost.com/howto/remove-empty-cells-excel-2007-2011-spreadsheets/)

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

[tsptalk]

Blanks are fine. Thanks.

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

http://www.tsptalk.com/images/mb/062415f.gif

[userque]

Blanks are fine. Thanks.

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

http://www.tsptalk.com/images/mb/062415f.gif

Got it. Is the order of column C important at this stage?

[tsptalk]

Got it. Is the order of column C important at this stage?
Not at all. TY!

[userque]

Not at all. TY!

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:

[burrocrat]

i'm just glad "burrocrat" doesn't show up in either column a or column b at this point, yet.

[RealMoneyIssues]

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!!

[tsptalk]

That will work. Thanks!


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:

[userque]

That will work. Thanks!

Great, give me several minutes or so.

[userque]

That will work. Thanks!

Any headers on columns A & B? Just the top row, if any?

[userque]

ok, it's ready for you to test it out...

just want to tweak it for you if you have headers.?

[tsptalk]

Thanks. Sorry... had to run a minute. Nothing pretty. I just need something to create the list.

[userque]

Thanks. Sorry... had to run a minute. Nothing pretty. I just need something to create the list.

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.

[tsptalk]

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

[userque]

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

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.

[userque]

Tom, while I wait for your PM:

34208

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.

[tsptalk]

So if column A has 100 rows the equation for C1 is:

=IFERROR(IF(MATCH(A1,OFFSET($B$2,0,0,100,1),0),"",A1 ),A1)


And if column B has 50 rows, would D1 be:

=IFERROR(IF(MATCH(B1,OFFSET($A$1,0,0,50,1),0),"",B1),B1)


Or would it be:

=IFERROR(IF(MATCH(B1,OFFSET($A$1,0,0,100,1),0),"",B1),B1)

[userque]

So if column A has 100 rows the equation for C1 is:

=IFERROR(IF(MATCH(A1,OFFSET($B$2,0,0,100,1),0),"",A1 ),A1)

Nope. That red 100 is for column B (Note $B$2)


And if column B has 50 rows, would D1 be:

=IFERROR(IF(MATCH(B1,OFFSET($A$1,0,0,50,1),0),"",B1),B1)

Naw. That number is for column A

Tom, I see $B$2 and $A$1. Does B have a header or something...and you changed it to 2? It was $B$1 when I wrote it. And if you did change it because of a header, then we have to change something else as well.

[k0nkuzh0n]

just use the countif function. If you count more than 1 of a name, ignore it with a blank. Once you've done that, use the Filter feature to filter out the blanks. Copy the new Column(s) and paste VALUES into a new sheet. done!

[k0nkuzh0n]

to combine two columns into 1:
=INDEX(A:B,ROUNDUP(ROW()/2,0),2-MOD(ROW(),2))



to ignore values that happen more than once:
=IF(COUNTIF(A:B,"="& Cell )=1, Cell,"")

The words Cell get replaced with the 1st equation.

end result:
=IF(COUNTIF(A:B,"="&INDEX(A:B,ROUNDUP(ROW()/2,0),2-MOD(ROW(),2)))=1,INDEX(A:B,ROUNDUP(ROW()/2,0),2-MOD(ROW(),2)),"")

Put it in the 3rd column, 1st cell, and drag it down. must drag it down to TWICE the length of the longest column, A or B.

[tsptalk]

Thanks, k0. userque's formula did the trick. I ended up needing one other change that I didn't account for originally, but your solution would have done it too. This was the final result:

2 columns, delete any name in column A if it was in column B. Or column C = (column A minus column B).


=IF(A2<>"",IFERROR(IF(MATCH(A2,OFFSET($B$1,0,0,100,1),0),"",A2 ),A2),"")

Thanks everyone, for your help!!

[jpcavin]

I need a drink...or several!:alcoholic: :alcoholic::alcoholic:

[burrocrat]

I need a drink...or several!:alcoholic: :alcoholic::alcoholic:

perfect timing, i was just gonna ask if i could get you one?

[jpcavin]

perfect timing, i was just gonna ask if i could get you one?

Good, I have few of these I'd like to redeem.

34227

[burrocrat]

Good, I have few of these I'd like to redeem.

34227

out where i live we call them 'wooden nickels' they are free drink tokens like a poker chip and you can get them at any bar/community center. the pool table gives the young kids something to do while the adults discuss 'business' at the long desk with a mirror behind it.

34229

i.would show you a real one from my collection, but i seemed to have used them all.