Pasting cells to Second Sheet

J

jay

I have a column that I want to search for a number, select, copy and
past the record that it is in to a second sheet at the first row.

Then do another search for a number, select the row that it is in and
copy it to the second row in the second sheet.

Then do another search for a number and so on.

However, if a number can't be found then leave the row blank in the
second sheet.
 
J

JulieD

Hi Jay

could you use VLOOKUP for this - the only requirement is that the "number"
you are searching for is in the leftmost column of the data you want on the
second sheet ..

for example
sheet 1
A B C D
1 123 Cat Meow Smootch
2 234 Dog Woof Spot
3 789 Mouse Squeak Ephratus

sheet 2
A B
1 234
then in B1
=IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$D$3,2,0)),"",VLOOKUP(A1,Sheet1!$A$1:$D$3,2,
0))
then in C1
=IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$D$3,3,0)),"",VLOOKUP(A1,Sheet1!$A$1:$D$3,3,
0))
then in D1
=IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$D$3,4,0)),"",VLOOKUP(A1,Sheet1!$A$1:$D$3,4,
0))

you can then enter all the numbers you want to find in column A, copy this
formula down & if there's a match then the information will be filled in, if
not then the rest of the row will be left blank

once you've done this, you can then select the whole of the second sheet and
copy it and then edit / paste special - values to change it from being
formulas to values.

Hope this helps
Cheers
JulieD
 
J

jay

JulieD said:
Hi Jay

could you use VLOOKUP for this - the only requirement is that the "number"
you are searching for is in the leftmost column of the data you want on
the second sheet ..

for example
sheet 1
A B C D
1 123 Cat Meow Smootch
2 234 Dog Woof Spot
3 789 Mouse Squeak Ephratus

sheet 2
A B
1 234
then in B1
=IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$D$3,2,0)),"",VLOOKUP(A1,Sheet1!$A$1 $D$3,2,
0))
then in C1
=IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$D$3,3,0)),"",VLOOKUP(A1,Sheet1!$A$1 $D$3,3,
0))
then in D1
=IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$D$3,4,0)),"",VLOOKUP(A1,Sheet1!$A$1 $D$3,4,
0))

you can then enter all the numbers you want to find in column A, copy this
formula down & if there's a match then the information will be filled in,
if not then the rest of the row will be left blank

once you've done this, you can then select the whole of the second sheet
and copy it and then edit / paste special - values to change it from being
formulas to values.

Hope this helps
Cheers
JulieD
Click to expand...




I have to study Vlookup. Don't know anything about it.


Here is my problem.

I have 40 numbers that I must search for in column C in an excel
spreadsheet.


x7659
a3433
q2322
upto 40

I would like to have these numbers put at the top of the macro
to make it easy to update the numbers as needed.

With some codes maybe I can set a variable to the number and use it.


Like
a1 = x7659
a2 = a3433
a3 = q2322
a4 = and so on....

These number will be at the top of the macro so I can just run the macro and
edit all 40 of them.


When the first number is found copy the record to the second sheet in the
first row.

Second number to the second row of the sheet2
Third number to the third row of the sheet2

But, if a number is not found that record would not be copied to the row in
the sheet2.



If I can do this then I think it will be very fast and I can organize the
sheet2 in any way I want to.
 
Top