Arranging records in excel (VLook Up?)

[Chelsea7]

Good day,

I'm trying to figure out how to look up the zip code under ZipA with the other headers where the second Zip lines up next to the ZipA records. In other words similar to Zip 10474 for WK 31 & WK 30. That just happened to be a coincidence, but I would like for example 10470 to line up next to the one under ZipB. WK 31 is sorted to a ranking but WK30 is to be compared with WK 31 for the same Zip. It's not to be sorted. It just needs to be next to week 31. I tried VLookup but it didn't do what I would like. What command should I be using for Excel 2010?



WK ZipA Ontime Failed WK ZipB Ontime Failed
31 10026 34 11 30 10467 115 48
31 10004 139 43 30 10466 52 21
31 10467 111 33 30 10470 45 18
31 10475 61 15 30 10458 84 30
31 10474 44 10 30 10474 35 9
31 10466 97 20 30 10032 60 14
31 10027 82 16 30 10027 92 20
31 10470 64 11 30 10473 83 18

If I'm not clear, please let me know and I will try to explain it better.
You assistance would be much appreciated. Thanks.

 

[combo]

What about using filters or pivot table? In the second case you will get report separated from the source data, what is more secure.

combo

 

[SkipVought]

Hi,

You'll need two tables.

The A table...
[pre]
WK ZipA Ontime Failed WKB ZipB OntimeB FailedB
31 10026 34 11
31 10004 139 43
31 10467 111 33
31 10475 61 15
31 10474 44 10
31 10466 97 20
31 10027 82 16
31 10470 64 11
[/pre]
...and the B table
[pre]
WK ZipB Ontime Failed
30 10467 115 48
30 10466 52 21
30 10470 45 18
30 10458 84 30
30 10474 35 9
30 10032 60 14
30 10027 92 20
30 10473 83 18
[/pre]

I've converted both these tables to Structured Tables, one of the best features Excel has introduced in the 2007 version. The A tables is Named tA and the B table is Named tB (*cough**cough*)

Here are the formulas to lookup the ZIP. BTW you enter or past the formula into a cell in the Structured Table and VOLA, y'all!!! IT FILLS THE ENTIRE COLUMN!
[pre]
WKB: =IFERROR(INDEX(tB[WK],MATCH([@ZipA],tB[ZipB],0),1),"")
ZIPB: =IFERROR(INDEX(tB[ZipB],MATCH([@ZipA],tB[ZipB],0),1),"")
OntimeB: =IFERROR(INDEX(tB[Ontime],MATCH([@ZipA],tB[ZipB],0),1),"")
FailedB: =IFERROR(INDEX(tB[Failed],MATCH([@ZipA],tB[ZipB],0),1),"")
[/pre]

And the result...
[pre]
WK ZipA Ontime Failed WKB ZipB OntimeB FailedB
31 10026 34 11
31 10004 139 43
31 10467 111 33 30 10467 115 48
31 10475 61 15
31 10474 44 10 30 10474 35 9
31 10466 97 20 30 10466 52 21
31 10027 82 16 30 10027 92 20
31 10470 64 11 30 10470 45 18

[/pre]




Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]