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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,326
Office Version
  1. 365
Platform
  1. Windows
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,887
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
20,326
Office Version
  1. 365
Platform
  1. Windows
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,887
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,031
Messages
5,856,936
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top