Extracting data

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
Hi All:

This is just a sample of the database.
Sent Reports data upload status.XLS
CDEFG
1BRANCHNAME2006-P9W32006-P9W22006-P9W12006-P8W4
2BirminghamYYYY
3BostonYYYY
4BuffaloYYYY
5CalgaryMISSINGYYY
6DubaiYYYY
7DusseldorfYMISSINGYY
8EdmontonMISSINGYYY
9FargoMISSINGYMISSINGY
10FortMeyersMISSINGYYY
11FrankfurtMISSINGYYMISSING
12FresnoYYYY
13GrandRapidsYYYY
14Halifax/MonctonYYYY
15HamburgYYYY
16HoustonYYYY
Status


What I need to do is extract only the records that don't have Y for Yes. Therefore, the desired output should only return the records that have 'Missing' or lets say something "No". Can someone please tell me how this can be done in excel. The database is huge so it is tough to filter column by column.

Thanks in advance for your help.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,388
Actually, Advanced Filter can still be used. Try the following...

H1: leave blank

H2:

=COUNTIF(D2:G2,"<>Y")

Data > Filter > Advanced Filter

List range: $C$1:$G$16

Criteria: $H$1:$H$2

Click Ok

Hope this helps!
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Can you show what the above data would produce?

Would a VBA solution be OK?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi baggarwal

I did an example of a solution. Please adjust the ranges for your case

All the 3 formulas are array formulas and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

In I2:
Code:
=INDEX($C$2:$C$16,INT(SMALL(IF($D$2:$G$16<>"Y",ROW($D$2:$G$16)-ROW($C$1)+(COLUMN($D$2:$G$16)-COLUMN($C$1))/10000),ROW()-ROW($I$1))))

In J2:
Code:
=INDEX($D$1:$G$1,10000*MOD(SMALL(IF($D$2:$G$16<>"Y",ROW($D$2:$G$16)-ROW($C$1)+(COLUMN($D$2:$G$16)-COLUMN($C$1))/10000),ROW()-ROW($I$1)),1))

In K2:

Code:
=INDEX($D$2:$G$16,INT(SMALL(IF($D$2:$G$16<>"Y",ROW($D$2:$G$16)-ROW($C$1)+(COLUMN($D$2:$G$16)-COLUMN($C$1))/10000),ROW()-ROW($I$1))),10000*MOD(SMALL(IF($D$2:$G$16<>"Y",ROW($D$2:$G$16)-ROW($C$1)+(COLUMN($D$2:$G$16)-COLUMN($C$1))/10000),ROW()-ROW($I$1)),1))

Hope this helps
PGC
Book1
CDEFGHIJK
1BRANCHNAME2006-P9W32006-P9W22006-P9W12006-P8W4BRANCHNAMECODEMESSAGE
2BirminghamYYYYCalgary2006-P9W3MISSING1
3BostonYYYYDusseldorf2006-P9W2MISSING6
4BuffaloYYYYEdmonton2006-P9W3MISSING2
5CalgaryMISSING1YYYFargo2006-P9W3MISSING3
6DubaiYYYYFargo2006-P9W1MISSING7
7DusseldorfYMISSING6YYFortMeyers2006-P9W3MISSING4
8EdmontonMISSING2YYYFrankfurt2006-P9W3MISSING5
9FargoMISSING3YMISSING7YFrankfurt2006-P8W4MISSING8
10FortMeyersMISSING4YYY
11FrankfurtMISSING5YYMISSING8
12FresnoYYYY
13GrandRapidsYYYY
14Halifax/MonctonYYYY
15HamburgYYYY
16HoustonYYYY
Sheet4
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,388
pgc01's formulas can be shortened as follows...

I2, copied down:

=INDEX($C$2:$C$16,SMALL(IF($D$2:$G$16<>"Y",ROW($D$2:$G$16)-ROW($D$2)+1),ROWS(I$2:I2)))

J2, copied down:

=INDEX($D$1:$G$1,SMALL(IF(INDEX($D$2:$G$16,MATCH($I2,$C$2:$C$16,0),0)<>"Y",COLUMN($D$2:$G$16)-COLUMN($D$2)+1),COUNTIF($I$2:$I2,$I2)))

K2, copied down:

=INDEX($D$2:$G$16,MATCH($I2,$C$2:$C$16,0),SMALL(IF(INDEX($D$2:$G$16,MATCH($I2,$C$2:$C$16,0),0)<>"Y",COLUMN($D$2:$G$16)-COLUMN($D$2)+1),COUNTIF($I$2:$I2,$I2)))

These formulas also need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi baggarwal, Domenic

A remark for baggarwal.

pgc01's formulas can be shortened as follows...

maybe we could add

"assuming that Branch Name never repeats"

which may well be the case, although baggarwal says "The database is huge".

So, baggarwal, if Branch Name never repeats use Domenic's formulas, they are more compact and efficient.

If this is just a small sample and you have several reports from the same branch with, for example, different dates, in that case, if I'm not mistaken, you have to use mine.

Cheers
PGC
 

Watch MrExcel Video

Forum statistics

Threads
1,114,384
Messages
5,547,638
Members
410,805
Latest member
Ginoji
Top