Need to Create a Table based on One Criteria

[dwgnome]

Hi all,

I have an existing Table with 8 to 10 columns of data. Instead of using Excel's Table Filter feature or Pivot Table, I would like to create a new table "filtered" on one of the original columns and have all of the data rows and (columns) displayed on another sheet using formulas (without "helper" columns). For simplicity, the example below shows just 5 of the columns.

Overall Usage (sheet name)

PCN PartID WC 2008 Use 2008 Issue

8430109 SEA37 124 1 0.015
8430109 SEA37 124 3 0.045
8430109 SEA37 152 2 0.030
8430123 RES44 124 6 0.200
8430123 RES44 C09 6 0.200
8430123 RES44 P11 2 30.00
8400188 PAI23 124 5 3.66
8400188 PAI23 542 2 No PCN

The No PCN refers to a lookup on another table when there is no match to the PCN on this table.


What I am needing on another worksheet called Sheet 124:

A1: WC=124 (filtered on this in WC column – which is changeable to create different tables)

PCN PartID WC 2008 Use 2008 Issue

8430109 SEA37 124 1 0.015
8430109 SEA37 124 3 0.045
8430123 RES44 124 6 0.200
8400188 PAI23 124 5 3.66


I have tried various INDEX/MATCH combo’s but can only get the first instance of the data.

Please Login or Register  to view this content.

copied across. Column H is where WC resides from the original table.

Of course for even more flexibility (and yet more complicated formula), I would like to make the columns variable by using drop down list headers and have the data match that as well.

I need all instances of the data sorted on. This is on 3000+ rows.

Thanks in advance.

[JBeaucaire]

This requires a trick formula and I've laid out your sample data in a workbook so you can see it in action. It is an array formula, highlighted in yellow, when you edit it you must confirm your changes with CTRL-SHIFT-ENTER to keep the array active. Then you can copy that first cell down and over to the right as far as desired.

=IF(ROW() - 3 > $C$1, "", INDEX('Overall Usage'!A$1:A$20, SMALL(IF('Overall Usage'!$C$1:$C$20 = $B$1, ROW('Overall Usage'!$A$1:$A$20), ""),ROW() - 3)))

[dwgnome]

Thank you so very much JBeaucaire, it worked perfectly.

I think I understand what you did. I think the first ROW()-3, it is used to "loop" through all of the possible occurrences of WC (Counter), however, I fail to understand what the second ROW()-3 is used for, since the last argument in the INDEX function is expecting a column reference.

Now since I have columns from Sheet Overall Usage I don't necessarily want to carryover that are in-between ones shown in the example, I would like the data that is pulled into Sheet 124 to be also based on the column header in Sheet 124. I know I can just hide the columns, but that would be too easy. Is this possible via formula?

Thank you again for your fast and accurate response.

[JBeaucaire]

The second ROW() function is the final parameter in the SMALL() function. The first formula is asking for the FIRST instance of matching info, because ROW()-3 in row4=1. The next time it would equal 2...etc.

I don't know how to respond to the rest.

[dwgnome]

Needing a solution to my second part of this thread. Building on what JBeaucaire has accomplished, how can I via formula control which columns to display using drop down list in the headers. For I have many other columns in my real case file that would automatically carryover that I do not want to have in the table. See attached file.

[JBeaucaire]

One assume you know which columns you want to show on the Sheet124? You only have to create the formulas for the columns you want.

The array formula for bringing over column A is:
'=IF(ROW()-3>$C$1,"",INDEX('Overall Usage'!A$1:A$20,SMALL(IF('Overall Usage'!$C$1:$C$20=$B$1,ROW('Overall Usage'!$A$1:$A$20),""),ROW()-3)))

That part in red is all that you are adjusting as you create the format of the Sheet124. I just showed ALL the columns, you don't have to do that. I imagine in your real sheet you will also be expanding the ranges to WAY more than 20 rows. Performance will begin to slow as you expand these formulas and start to include them in more and more cells.

I know of no simple way to "turn columns on / off" with a drop down. In this layout we're already using a very heavy-lifting formula. If you start to add in INDIRECT() references to auto-change the red section and then apply this to a very large data set, I can foresee performance issues for sure.

Can't you simply design which columns you want and include those, skip the rest?

[dwgnome]

Yes, I normally do know which columns on Sheet124 I would like. The problem is that the actual spreadsheet has multiple years on it as well. In order to do reports for various years, I would have to go back and build in extra columns or change formulas to correspond to the correct columns that refer to the original table. Other users of this spreadsheet will not have the necessary know-how to make these formula adjustments.

Is there not a way to associate the INDEX item in red to the corresponding header title? I am willing to take the performance hit if you could just show how this can be done.

Thanks.

[JBeaucaire]

OK, the new array formula in A4 then copied around:

=IF(ROW() - 3 > $C$1, "", INDEX(INDIRECT("'Overall Usage'!R1C" & MATCH(A$3, 'Overall Usage'!1:1, 0) & ":R20C" & MATCH(A$3, 'Overall Usage'!1:1, 0), FALSE), SMALL(IF('Overall Usage'!$C$1:$C$20=$B$1,ROW('Overall Usage'!$A$1:$A$20), ""), ROW() - 3)))


What a beast! You'll have to adjust all the R20 references to match how far down the Overall Usage sheet you really want to evaluate...

[dwgnome]

Wow! Quite the formula. You are the genius - thank you it worked. I had to force the row reference to absolute before copying down. Changed 'Overall Usage'!1:1, 0) to 'Overall Usage'!$1:$1, 0).

Is it possible to explain the new trick within INDIRECT and what it is really doing. Specifically what is R1C, 1:1 and R20C? These seem to be Row and Column designators where R1C1 is the same as A1. So if my original Table set is 1000 rows, I need to change to R1000C?

[dcwilson]

How did you create the drop down boxes for the headers?

Thanks.

[JBeaucaire]

Click on the box with the drop down you want to analyze, then open the Data > Validation window, check the settings.