Lookup of Cells and transferring them to make a list

D

Dan

Can you please tell me how to lookup cells and transfer them to a list,

eg

Data in sheet 1
A B

2 100
5 50
4 75
2 250
5 95
2 60

I want to lookup the value "2" in the Column A of the data in sheet 1 and
and transfer to another sheet to appear as below(keeping in mind i dont want
blanks cells where the other A values were);

Sheet 2
A B

2 100
2 250
2 60

Thank you in advance
Dan
 
M

MartinW

Hi Dan,

The easiest way for that is to goto Data>Autofilter and filter for 2
on column A then copy and paste the result to your new sheet.

HTH
Martin
 
D

Dan

Thanks, I got to that part before posting but found that my data is
continually growing ad, as bad as this sounds, dont have time to refresh the
filter. I will also need to use this feature with lots of data. I also need
run a cumulative totals. there lay several problems.

So my question is, Is there a formula that will shift the data to the new
sheet and bunch the selected data while completely leaving out the unwanted
data.

Thanks again

Dan
 
M

Max

Dan said:
So my question is, Is there a formula that will shift the data to the new
sheet and bunch the selected data while completely leaving out the unwanted
data.
Click to expand...

One play which will return exactly what you're after ..

Assume source data in Sheet1,
cols A and B, data from row1 down to a max row 100 (say)
(key col is col A = criteria value)

In Sheet2,

Let D1 house the criteria input, eg: 2

Put in A2:
=IF(ROW(A1)>COUNT($C:$C),"",INDEX(Sheet1!A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy A2 to B2

Put in C2:
=IF(OR(Sheet1!A2="",$D$1=""),"",IF(Sheet1!A2=$D$1,ROW(),""))
(Leave C1 empty)

Then just select A2:C2, copy down to C100
to cover the max expected extent of data in Sheet1

Cols A & B will return the required results, all neatly bunched at the top
(Hide away the criteria col C if desired)
 
M

MartinW

Hi again Dan,
I will also need to use this feature with lots of data. I also need
Click to expand...
run a cumulative totals. there lay several problems.<

As you say 'there lay several problems'. The short answer is YES! what
you are trying is very achievable, and it probably doesn't involve
very complicated formulae, just some clear thinking.

You say you are trying to 'shift' data from one sheet to another.
Don't think of it that way, think more along the lines of the end
result as in what does that new sheet need to get the result you want.

Post again with more detail of your desired results and I'm sure
someone will come up with what you need.

HTH
Martin
 
Top