Consolidating Rows based on Cell Numbers

[carlsondaniel]

I have rows cells that I need to consolidate to another page by a
reference number found within the list. For example:
Sheet 1
A B C D E
Description Invoice Number Code Amount Date
Purchase 1 xxx 15000 $4.00 8/2/2006
Purchase 2 xxx 15001 $ 5.00 3/5/2005
Purchase 3 xxx 15002 $6.00 4/6/2006
Purchase 4 xxx 15003 $7.00 7/30/2006

Sheet 2
Codes to Pull Out
15001
15003

A B C D
E
Description Invoice Number Code Amount Date
Purchase 2 xxx 15001 $5.00 3/5/2005
Purchase 3 xxx 15003 $6.00 4/6/2006



Essentially, if a number matches in Column C, then I need to pull out
the whole row. There are multiple numbers in Column C that I need to be
pulled simultaneously. Is there a way I can do a VLOOKUP for the whole
row without leaving spaces between? Thank You!

 

[Guest]

You could use an Advanced Filter to extract those records. See Debra
Dalgleish's website for instructions:
http://www.contextures.com/xladvfilter01.html#ExtractWs

Post back with any questions.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Another option using non-array formulas which delivers it dynamically ..

A sample construct is available at:
http://www.savefile.com/files/65699
Auto consolidate rows by input codes in new sht.xls

Assume source data is in sheet named: X, cols A to E, data from row2 down,
where col C = Code (the key col)

In a new sheet: Y,

Label A1 as say: Input codes
In A2 down would be the input codes of interest,
say: 15001, 15003 (codes can be input in any order)

Paste the same col headers from X into C1:G1

Then put in B2:
=IF(SUMPRODUCT(--ISNUMBER(MATCH(X!C2,A:A,0)))>0,ROW(),"")
(Leave B1 empty)

Put in C2:
=IF(ROW(A1)>COUNT($B:$B),"",INDEX(X!A:A,MATCH(SMALL($B:$B,ROW(A1)),$B:$B,0)))
Copy C2 to G2

Select B2:G2, copy down to cover the max expected data extent in X, say down
to G100. Hide away col B. Cols C to G will return the required results from
X, ie lines with codes matching those input in A2 down. All result lines will
be neatly bunched at the top.

 

[Guest]

In Y, just format the Amount & Date cols to taste ..


---

 

[Guest]

Slight tweak to:

Then put in B2:
=IF(SUMPRODUCT(--ISNUMBER(MATCH(X!C2,A:A,0)))>0,ROW(),"")

Put in B2:
=IF(ISNUMBER(MATCH(X!C2,A:A,0)),ROW(),"")

The sumproduct wasn't necessary

---

 

[Guest]

Note below was sent in reply to OP ..


---
------- :: ----------
Carl,

Pl follow up in the newsgroup thread. It's always simpler to use entire col
refs wherever possible. It'll also work for fixed ranges, of course, but we
have to be careful that these are consistently structured.

For your adapted formula below -- I presume this is the starting cell's
formula -- we should always use ROW(A1), not ROW(G21). ROW(A1) is used as an
incrementer within the formula. It returns the numeric series: 1,2,3 as we
copy down. You can see this by putting: =ROW(A1) in any cell, then copy down
from that cell. Try it with the change. Let me know in the newsgroup thread.

=IF(ROW(G21)>COUNT($H$21:$H$1000),"",INDEX('Actual Costs'!$I$21:$I$1000,MATCH(SMALL($H$21:$H$1000,ROW(G21)),$H$21:$H$1000,0)))

Rgds
Max

Max,

Thank you very much for the help. I am still having issues with the
formula. Do both sheets need to start in A1? I make both sheets start
in G22 (other stuff in sheets) but it is still pulling other numbers.
I made the formulas look like this..

The "B" Column

=IF(ISNUMBER(MATCH('Actual
Costs'!L22,$G$21:$G$100,0)),ROW(),"")

The "C through G" Columns.

=IF(ROW(G21)>COUNT($H$21:$H$1000),"",INDEX('Actual
Costs'!$I$21:$I$1000,MATCH(SMALL($H$21:$H$1000,ROW(G21)),$H$21:$H$1000,0)))

Can the arrays have a starting cell and ending cell or do they have to
be the whole cell (A:A)?

Any thoughts would be a great help.

Thanks again.

----- :: ---------