Create list of rows based on cell contents of specific column

Question

Create list of rows based on cell contents of specific column

I'm not quite sure how to best describe what I'm trying to do, but here goes...

I've got a table where the first column is a list of people. The following two columns are characteristics attributed to each person - for this example lets say eye color and hair color, and for each there are three options that can be chosen.

What I'd like to be able to do is go from that table to a new table, or list, of the names grouped into columns based on eye color, and then separate table or list of the names grouped into columns based on hair color.

Is there any way to automate the creation of the groupings tables/lists such that if I change someone's eye color, their name would move to a different column in the eye color grouping table/list?

Thanks,

Scott

iMac with Retina 5K display, macOS Sierra (10.12.4)

Posted on Aug 30, 2017 12:36 PM

Reply

Answer 1

SGIII

Level 8

37,744 points

Aug 30, 2017 8:37 PM in response to A. Scott McCulloch

Here is one way, using two helper index columns in the table with the data, and one formula in each of the tables where you are listing by attribute.

Add two columns to the right of your data. In D2, I have:

=B2&COUNTIF(B$1:B2,B2)

Fill that formula down and then right to column E.

In B2 of the 'Eye' table I have:

=INDEX(Data::$A,MATCH(A$1&ROW()−1,Data::$D,0))

I filled that down and right.

In B2 of the 'Hair' table I have:

=INDEX(Data::$A,MATCH(A$1&ROW()−1,Data::$E,0))

I filled that down and right.

To suppress the display of the red warning triangles simply wrap the last two formulas in IFERROR.

=IFERROR(<the original working formula>,<what to display if error>)

So in B2 of 'Eye' table, filled down and right:

=IFERROR(INDEX(Data::$A,MATCH(A$1&ROW()−1,Data::$D,0)),"")

In B2 of 'Hair' table, filled down and right:

=IFERROR(INDEX(Data::$A,MATCH(A$1&ROW()−1,Data::$E,0)),"")

Giving this:

If this turns out to be too much work, don't forget you can simply use the built-in filter features in Numbers to quickly display subsets of your data. For example, to list people with brown eyes, all you have to do is this:

SG

Reply

Answer 2

SGIII

Level 8

37,744 points

Aug 30, 2017 8:37 PM in response to A. Scott McCulloch

It gets more complicated with combinations!

I was able to do it by adding two more columns, like this:

The formula in F2, filled down, is:

=B2&"/"&C2

The formula in G2, filled down, is:

=F2&COUNTIF(F$1:F2,F2)

The COUNTIF is using the same "expanding range" as in previous posts.

In A2 of the 'Combo' table, similar to other 'Eye' and 'Hair' tables, is:

= IFERROR(INDEX(Data::$A,MATCH(A$1&ROW(cell)−1,Data::$G,0),column-index,area-inde x),"")

The MATCH part of the formula finds the row number where the combo listed on line 1 concatenated to the formula's row number minus 1 (to account for the Header Row) occurs in column G of the 'Data' table. It feeds that row number to INDEX, which looks up the corresponding value in column A of the 'Data' table.

And of course don't forget you could just filter on two columns to get lists of people matching each combination, without worrying about formulas:

Reply

Answer 3

Aug 30, 2017 1:55 PM in response to A. Scott McCulloch

I've manage to get part way there.

IF(eye color Bob="blue",'Individual Characteristics'::$A2,"")

then copy that for each row so I have something like:

IF(eye color Bob="blue",'Individual Characteristics'::$A2,"")

IF(eye color Jane="blue",'Individual Characteristics'::$A3,"")

IF(eye color Karen="blue",'Individual Characteristics'::$A4,"")

IF(eye color Jim="blue",'Individual Characteristics'::$A5,"")

and the next column would be:

IF(eye color Bob="brown",'Individual Characteristics'::$A2,"")

IF(eye color Jane="brown",'Individual Characteristics'::$A3,"")

IF(eye color Karen="brown",'Individual Characteristics'::$A4,"")

IF(eye color Jim="brown",'Individual Characteristics'::$A5,"")

The problem with this is that I get lists with gaps, e.g.,

Blue Brown

Bob

Jane

Karen

Jim

When what I'd like to end up with is

Blue Brown

Bob Jane

Jim Karen

Any ideas? Is what I want even possible?

Reply

Answer 4

Aug 30, 2017 4:44 PM in response to SGIII

I've got that working... now I want to see if I can make a list of all combinations of eye color and hair color.

To make an index column with the combination, I tried this:

=CONCATENATE(B2,"/",C2)&COUNTIF(B$1:B2&C$1:C2,B2&C2)

However it's not counting up... instead I'm getting

blue/brown1

blue/blond1

brown/brown1

blue/brown1

brown/brown1

I also tried

=CONCATENATE(B2,"/",C2)&COUNTIF(B$1:C2,B2&C2)

and that returned all 0 in place of 1

I must have missed something in the logic.

Reply

Answer 5

Aug 30, 2017 2:00 PM in response to A. Scott McCulloch

post an example of the input data. Select the table, copy, then return here and paste.

Reply

Answer 6

Aug 30, 2017 3:46 PM in response to SGIII

Thanks SG!

I've never worked with INDEX or MATCH, so it's very helpful to see those examples. I will give this approach a try.

Reply

Answer 7

Aug 30, 2017 5:45 PM in response to SGIII

Ah, yes... that makes sense to do a combo column and then index from that - thanks!!

Reply