formula or macro???

[tweetiemaster]

i am working on a project that i need excel to extract specific data from one sheet and add it to another sheet based on the value of the cell. for example. one column may have 300 rows of data but i may only need 15-20 rows out of that column depending on what the value is. or to make it simpler if i have 300 apples and some of those apples are labeled as bad apples i only want the bad apples from that column to show on another sheet. i have found an if statment similar to what i was looking for but it would only work on the entire row and not just the column. this particular tab is updated weekly (my raw data tab) each week adds 7 columns to the wooksheet and every other column has different critiera that needs to be met.

i basically need something that would say "if column B is not in the acceptable range then select column A and B and copy it to another worksheet"

Just FYI using Access is not an option for this particular project

 

[Aladin Akyurek]

i am working on a project that i need excel to extract specific data from one sheet and add it to another sheet based on the value of the cell. for example. one column may have 300 rows of data but i may only need 15-20 rows out of that column depending on what the value is. or to make it simpler if i have 300 apples and some of those apples are labeled as bad apples i only want the bad apples from that column to show on another sheet. i have found an if statment similar to what i was looking for but it would only work on the entire row and not just the column. this particular tab is updated weekly (my raw data tab) each week adds 7 columns to the wooksheet and every other column has different critiera that needs to be met.

i basically need something that would say "if column B is not in the acceptable range then select column A and B and copy it to another worksheet"

Just FYI using Access is not an option for this particular project

Sheet1 (source)

ExtractList tweetiemaster.xls
	A	B	C	D
1	CODE	NAME
2	400	murphy
3	400	smith
4	200	johnson
5	400	smith
6	200	henry
7	400	cash
8	400	law
9	400	smith
10	400	rushton
11	400	rolls
12	500	smith
13	100	hartman
14	200	smith
15
Sheet1

Sheet2 (destination)

ExtractList tweetiemaster.xls
	A	B	C	D
1	CODE
2	400
3	8
4	NAME
5	murphy
6	smith
7	smith
8	cash
9	law
10	smith
11	rushton
12	rolls
Sheet2

A3:

=COUNTIF(Sheet1!A2:A14,A2)

A5:

Control+shift+enter...

=IF(ROWS($A$5:A5)<=A$3,INDEX(Sheet1!$B$2:$B$14,SMALL(IF(Sheet1!$A$2:$A$14=A$2,ROW(Sheet1!$A$2:$A$14)-ROW(Sheet1!$A$2)+1),ROWS($A$5:A5))),"")

Then copy down.

 

[tweetiemaster]

i have been able to recreate this on my current project but the problem that i run to now is that the first cell is populated and all other cells are blank after i have copied the formula for the # of cells needed. what could cause this problem. the values flashed for a second and that was it.

 

[Aladin Akyurek]

i have been able to recreate this on my current project but the problem that i run to now is that the first cell is populated and all other cells are blank after i have copied the formula for the # of cells needed. what could cause this problem. the values flashed for a second and that was it.

Care to post the formulas as you implemented them?

 

[tweetiemaster]

this is how i have the formula.

=IF(ROWS($A$5:A5)<=B3,INDEX('ND'!$A$3:$A$255,SMALL(IF('ND'!$C$3:$C255=B$3,ROW('ND'!$A$3:A$255)-ROW('ND'!$A$3)+1),ROWS($A$5:A5))),"")

i have 7 out of 22 rows populated 2 are blank and the rest are now showing as #n/a

one thing i did do differently was after i typed in the formula i did the ctrl+shft+enter... not sure if this would have made a difference or not

 

[Aladin Akyurek]

this is how i have the formula.

=IF(ROWS($A$5:A5)<=B3,INDEX('ND'!$A$3:$A$255,SMALL(IF('ND'!$C$3:$C255=B$3,ROW('ND'!$A$3:A$255)-ROW('ND'!$A$3)+1),ROWS($A$5:A5))),"")

i have 7 out of 22 rows populated 2 are blank and the rest are now showing as #n/a

one thing i did do differently was after i typed in the formula i did the ctrl+shft+enter... not sure if this would have made a difference or not

What do you have in B3?

And, did you do anything with the COUNTIF formula I also suggested?

 

[tweetiemaster]

b1 has the date
b2 has the total of the count if (in this case 22)
b3 has 1 this the value that all 22 items should have had on the previous sheet) similar to how you had code and 400 under it. i have everything going to the side
a1-a3 has the titles to the fields of b1-b3
b4 is my header row

 

[Aladin Akyurek]

b1 has the date
b2 has the total of the count if (in this case 22)
b3 has 1 this the value that all 22 items should have had on the previous sheet) similar to how you had code and 400 under it. i have everything going to the side
a1-a3 has the titles to the fields of b1-b3
b4 is my header row

A5:

Control+shift+enter...

=IF(ROWS($A$5:A5)<=B$2,INDEX('ND'!$A$3:$A$255,SMALL(IF('ND'!$C$3:$C255=B$3,ROW('ND'!$A$3:A$255)-ROW('ND'!$A$3)+1),ROWS($A$5:A5))),"")

and copy down.