Lookup cell in table to get entire row

  • Thread starter Thread starter Hall
  • Start date Start date
H

Hall

My source table has many rows. One of the columns in this table is
"Category". Each row's Category column is one of "A", "B", "C", or "D".

I want formulas to generate a second table with all the entire rows of the
source table, grouped by category. So first is all the rows with Category
A, then all the rows with Category B, etc.

There's a clever easy way to do this, right?

Thx y'all.
 
Hall,

Yeah. Just sort the table on the Category column. Be careful -- do not
select just the category column. Either select ONE CELL, or the entire
table. Or you may wish to copy/paste the entire table, and sort the second
one.

Or you may wish to make a linked table, which saves you the copy/paste when
the original data changes, though you still have to sort the second one.
Each cell in the linked table is linked to it's corresponding cell in the
original table (until the linked table has been sorted). Post back if you'd
like instructions on the linking.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net

Life may not be the party we hoped for, but while we
are here we might as well dance.
 
It sounds like you would probably like Pivot Tables for this.

Check out the Help files and these links:

http://www.contextures.com/tiptech.html

http://www.cpearson.com/excel/pivots.htm
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



My source table has many rows. One of the columns in this table is
"Category". Each row's Category column is one of "A", "B", "C", or "D".

I want formulas to generate a second table with all the entire rows of the
source table, grouped by category. So first is all the rows with Category
A, then all the rows with Category B, etc.

There's a clever easy way to do this, right?

Thx y'all.
 
OK, so its not quite what I was expecting. I thought there'd be a lookup
formula that can do this.

So how do you make a linked table? I assume I can have my source table be
left NOT sorted by Category column while the linked table IS sorted by
Category column.

Thanks!
 
Hall,
So how do you make a linked table?
Click to expand...

In the destination (where the linked table will be), select a cell, type =,
then switch to the source sheet, and click the corresponding cell. It
should put a reference to the cell. Press Enter; you're done with this
formula (link). Now use the fill handle (lower right of cell) to copy the
formula to all the other cells.

Note that if you make the linked table larger (where there empty cells in
the source table), you'll get 0. You can format them away (Format - Cells -
Number), or include an IF that will instead yield "" (empty text string).
Or you may be able to use Tools - Options - View - Zero values (applies on a
per-sheet basis, not the entire workbook), if appropriate for you.

You can now sort the linked table on the category column. The original
table will not be affected.

BTW, sorting the original table is commonly done. You just need to be
careful that you don't sort just one column (Excel will still do that,
ruining the table, though it has more safeguard warning messages than
before). Buttons that run a macro that sorts the table, placed in or around
the associated column heading, are handy. Or an event macro can sense
clicking in the heading of a column and perform the sort. Slick. It's not
unusual to sort a table in various ways as the needs arise.
 
Back
Top