Excel expert needed

Sort by newest Sort by date
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)
 
tsptalk said:
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)
Click to expand...

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

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

=IFERROR(IF(MATCH(B1,OFFSET($A$1,0,0,50,1),0),"",B1),B1)
Click to expand...

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.
 
Last edited:
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!
 
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.
 
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),"")
Click to expand...

Thanks everyone, for your help!!
 
jpcavin said:
Good, I have few of these I'd like to redeem.

View attachment 34227
Click to expand...

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.

View attachment 34229

i.would show you a real one from my collection, but i seemed to have used them all.
 
You must log in or register to reply here.
Back
Top