Forum Discussion
Solved
Getting data from one column that match criteria to other columns?
Hi There,
I have the following issue, I need to get the values from column C (total) and put it a column F,G and H depending on the R Number value in column A.
Some R Number repeat and some don't. The ones with earlier dates should be in T1 and the one with the latest date in T3.
I have filter the unique R Number to column E but I need help to get those values across.
For example the result ideally should be:
58544 - $10 - $80 - $130
58594 - $20 - $60 - $100
58549 - $30
59125 - $40
and so forth...
THANK YOU ALL
I suggest you start from scratch. In version 3, delete the formulae at the bottom of the data table (rows 10 to 42). These cause Excel to see 33 records with a blank date. Hence, it adds a blank year (the check box without a year number) to the filter window.
Now convert your data table (Ctrl-T) into a so-called structured table. If you now enter a new date in column E, the table will automatically expand and copy down the formulae from the previous row.
Then, change the data source for the PT to this table, called "Table2" in the attached example. It will also avoid blanks. Referring column A:H is okay as well, but then you'll end up with the blanks that need to be filtered out.
6 Replies
- Unless there's highly confidential data in that spreadsheet from which you took the screen grab, could you post the actual spreadsheet, so we don't need to re-create it in order to answer you?
- I just added the sample spreadsheet to the original post. Thank you
So this isn't exactly what you requested, but it's a simple solution that is elegant in that only one formula is needed for filling in each row. It uses the very new FILTER function, and as such requires the newest versions of Excel to operate.
I sorted your database in date order, first.
Then used the UNIQUE function to list the unique R numbers
Then this formula
=TRANSPOSE(
FILTER($C$2:$C$14,($A$2:$A$14=D2),"None")
)
The FILTER function in this case just gets every row in the database, in order (in this case, in date order), for each R number. Because FILTER is one of the "Dynamic Array" functions released in the last year, it will list every row of a database that meets the criteria. In this case, the only criterion I used was that of matching the R number.
TRANSPOSE just arrays what would have been a display cascading down "n" rows, and puts them across the columns.
An aside: Before I did this, I did write a FILTER function that first got the minimum date or the maximum date and combined that with the FILTER that got the rows that matched the R number. This worked for T1 and T3 for these two that had three instances; but it was "less elegant"--different formula for each instance--and I'm not sure added value.
Here, if you've not seen them, is an excellent YouTube video from Microsoft in which they introduced the Dynamic Array functions. https://www.youtube.com/watch?v=9I9DtFOVPIg
