Extract Data onto another sheet based on Criterias

[ameenuksg]

Hi

I have Data on a sheet that looks something like this:

Zip Code/Area Code	Order Numbers
PT	10073301 <tbody> </tbody>
MSW	10073302 <tbody> </tbody>
MSP	10073304 <tbody> </tbody>
WCP	10073305 <tbody> </tbody>
629613 <tbody> </tbody>	10078998
8198341 <tbody> </tbody>	10078989
129811 <tbody> </tbody>	10075643

<tbody>
</tbody>

I want to extract specific data to be transferred onto another sheet. Is there a formula I can use to transfer order numbers that bear these area codes PT, MSW, MSP & WCP onto another sheet. I have a long list that runs to row 3000 and more to be updated on daily basis. So I would like these numbers that bear the above mentioned area codes on another sheet so that I could separately work on them. I would like that sheet to be updated automatically instead filtering, copying and pasting.

Any help would be most appreciated. Thank you in advance.

 

[ameenuksg]

Hi

No.5, is only counting upto 25 rows with the specific area code and does not go beyond that number although I have many more rows with the area codes PT, MSW, MSP and WCP. Kindly how can I fix this

Thank you in advance!

 

[Aladin Akyurek]

Hi

No.5, is only counting upto 25 rows with the specific area code and does not go beyond that number although I have many more rows with the area codes PT, MSW, MSP and WCP. Kindly how can I fix this

Thank you in advance!

What does the following return?

=ROWS(Code)

 

[ameenuksg]

What does the following return?

=ROWS(Code)

Hi I pasted the above formula on Sheet 2, row B7 and it read as '244', is this is correct to return a number? which I dont know what number this relates to

 

[Aladin Akyurek]

Hi I pasted the above formula on Sheet 2, row B7 and it read as '244', is this is correct to return a number? which I dont know what number this relates to

Code as we defined it contains 244 rows. This is what 224 means.

You say that the formula

{=SUM(IF(ISNUMBER(MATCH(Code,$A$1:$A$4,0)),1))}

is "counting upto 25 rows." If this formula returns 25, that means it has counted all of the occurrences of the codes in A1:A4 and that count is 25.

 

[ameenuksg]

Code as we defined it contains 244 rows. This is what 224 means.

You say that the formula

{=SUM(IF(ISNUMBER(MATCH(Code,$A$1:$A$4,0)),1))}

is "counting upto 25 rows." If this formula returns 25, that means it has counted all of the occurrences of the codes in A1:A4 and that count is 25.

Actually, there are more than 25 rows with the occurrences of the codes in A1:A4. 56 more but its not counting these 56 more. Anyway to make the formula to include those too?

 

[Aladin Akyurek]

Actually, there are more than 25 rows with the occurrences of the codes in A1:A4. 56 more but its not counting these 56 more. Anyway to make the formula to include those too?

The set up is such that they should be included automatically. If 56 is the correct count, there is a problem (a) how Code is defined or (b) the codes in Code do somehow not match the codes in A1:A4.

What do you get with the following?

=SUM(IF(ISNUMBER(MATCH(TRIM(Code),$A$1:$A$4,0)),1))

which must be confirmed with control+shift+enter, not just with enter.

 

[ameenuksg]

The set up is such that they should be included automatically. If 56 is the correct count, there is a problem (a) how Code is defined or (b) the codes in Code do somehow not match the codes in A1:A4.

What do you get with the following?

=SUM(IF(ISNUMBER(MATCH(TRIM(Code),$A$1:$A$4,0)),1))

which must be confirmed with control+shift+enter, not just with enter.

Nope this didnt work

 

[Aladin Akyurek]

Nope this didnt work

That is not what I'm asking. What is the count that that formula with TRIM returns?

 

[ameenuksg]

That is not what I'm asking. What is the count that that formula with TRIM returns?

I am sorry, it showed the same results as without the TRIM function.

 

[Aladin Akyurek]

I am sorry, it showed the same results as without the TRIM function.

Let's see: What does the following give?

=ROWS(Order)