Extracting data

[baggarwal]

Hi All:

This is just a sample of the database.

Sent Reports data upload status.XLS
	C	D	E	F	G
1	BRANCHNAME	2006-P9W3	2006-P9W2	2006-P9W1	2006-P8W4
2	Birmingham	Y	Y	Y	Y
3	Boston	Y	Y	Y	Y
4	Buffalo	Y	Y	Y	Y
5	Calgary	MISSING	Y	Y	Y
6	Dubai	Y	Y	Y	Y
7	Dusseldorf	Y	MISSING	Y	Y
8	Edmonton	MISSING	Y	Y	Y
9	Fargo	MISSING	Y	MISSING	Y
10	FortMeyers	MISSING	Y	Y	Y
11	Frankfurt	MISSING	Y	Y	MISSING
12	Fresno	Y	Y	Y	Y
13	GrandRapids	Y	Y	Y	Y
14	Halifax/Moncton	Y	Y	Y	Y
15	Hamburg	Y	Y	Y	Y
16	Houston	Y	Y	Y	Y
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.

 

[Domenic]

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]

Can you show what the above data would produce?

Would a VBA solution be OK?

 

[pgc01]

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:

=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:

=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:

=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
	C	D	E	F	G	H	I	J	K
1	BRANCHNAME	2006-P9W3	2006-P9W2	2006-P9W1	2006-P8W4		BRANCHNAME	CODE	MESSAGE
2	Birmingham	Y	Y	Y	Y		Calgary	2006-P9W3	MISSING1
3	Boston	Y	Y	Y	Y		Dusseldorf	2006-P9W2	MISSING6
4	Buffalo	Y	Y	Y	Y		Edmonton	2006-P9W3	MISSING2
5	Calgary	MISSING1	Y	Y	Y		Fargo	2006-P9W3	MISSING3
6	Dubai	Y	Y	Y	Y		Fargo	2006-P9W1	MISSING7
7	Dusseldorf	Y	MISSING6	Y	Y		FortMeyers	2006-P9W3	MISSING4
8	Edmonton	MISSING2	Y	Y	Y		Frankfurt	2006-P9W3	MISSING5
9	Fargo	MISSING3	Y	MISSING7	Y		Frankfurt	2006-P8W4	MISSING8
10	FortMeyers	MISSING4	Y	Y	Y
11	Frankfurt	MISSING5	Y	Y	MISSING8
12	Fresno	Y	Y	Y	Y
13	GrandRapids	Y	Y	Y	Y
14	Halifax/Moncton	Y	Y	Y	Y
15	Hamburg	Y	Y	Y	Y
16	Houston	Y	Y	Y	Y
Sheet4

 

[Domenic]

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]

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