Create a 3rd sheet containing cells in both Sheets 1 and 2

  • Thread starter Thread starter Roadtripper902902V3V
  • Start date Start date
R

Roadtripper902902V3V

I have 2 sheets. They both contain a list of Account Numbers. What I want to
do is to have a 3rd sheet created containing only those account numbers that
are found in both Sheet 1 and Sheet 2.

For example:

Sheet 1
Account Numbers
AAAAA
BBBBB
CCCCC
DDDDD
EEEEE
FFFFFF
9999999
GGGGGG
HHHHHH
IIIIIII
JJJJJJ
KKKKKK
LLLLLLL
333333
444444


Sheet 2
Account Numbers
1111111
KKKKKKK
222222
EEEEEEE
999999
XXXXXXX
TTTTTTTT
AAAAAA
FFFFFFFF

Sheet 3
AAAAAAA
EEEEEEEE
FFFFFFF
9999999
KKKKKKKK
 
By the way, I am a REAL BIG beginner with excel, so please provide some
hand-holding in the instructions. 8-) thank you.
 
Or if it would be easier to have all the info on one sheet,
I could put info on Sheet 2 into some columns on Sheet 1. And I do not mind
having the Sheet3 data (the "pairs") onto some colum(s) on sheet 1, as well.
 
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
 
Presuming that there's no duplicate account numbers in either Sheet1's data
or in Sheet2's data, you could try this easy formulas option to carve out the
expected results

In Sheet3,
In A2 down, do a copy n paste of the data from Sheet1/2, one below the other
(the pastes can be in any sequence). Done in 5 secs flat.

Put in B2:
=IF(AND(COUNTIF(A:A,A2)>=2,COUNTIF(A$2:A2,A2)<2),ROW(),"")

Put in C2:
=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROWS($1:1))))
Copy B2:C2 down to the last row of data in col A. Hide/minimize col B. Col A
will return the expected results all neatly packed at the top. Success? hit
the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Thanks, everyone.
I was able to find the solution by putting this in
C2: =VLOOKUP(A2,$B$2:$B$400,1,FALSE)
C3: =VLOOKUP(A3,$B$2:$B$400,1,FALSE)

Column 1 had all the account numbers from one source. (last row: 348)
Column 2 had all the acount numbers from a different source. (last row 311)
 
Back
Top