Forum Discussion
Populating a new table with data that match criteria from another table.
Vulkan51
I've attached the sample file.
If you're using Microsoft 365, Filter function you should use to sort out the 1st and 2nd semester data.
Example for your 1st semester client column
=FILTER(A2:A100, E2:E100="1st", "No Data")
Since I'm currently using a version of 2019, a combination of INDEX and SMALL function is my approach
Formula in Column H for 1st Semester Client:
{=IFERROR(INDEX(A$2:A$11, SMALL(IF($E$2:$E$11="1st", ROW(A$2:A$11)-ROW($A$2)+1), ROW(A1))), "")}1. After typing the formula, instead of pressing Enter key, press CTRL + SHIFT + ENTER to indicate it's an array formula.
2. Then drag the formula (by dragging the small green square on bottom-right corner of selected cell) down until the end of your table,
3. Then drag it again to the right 'till the MONTH column of the 1st semester.
4. After step 3, change the format of number appeared on Month column into Month's name.
Hi, I found this thread and have a similar problem. I was wondering if you might be able to take a look and see if you can spot the solution?
We use Excel to manage logs from many agricultural trials. There is a central 'live' table where live data for all trials gets sent to, but ultimately only the relevant data needs to be sent to each individual trial excel file in a table. Each Trial has a unique code, and I'm trying to use the filter function to populate a table on each individual trial file with data from the live central data table, using the unique code as an identifier.
Here is a portion of the table:
And here is what's currently working. I'm using the RIGHT function to include just the right 11 characters in the "Trial" column in the FILTER 'include' function, which is the trial code, to pull the relevant data across:
=FILTER(OnlineSprayBookAllData[[Date]:[Photo or Video]],(RIGHT(OnlineSprayBookAllData[Trial],11))=Table5[@SITE1],"Please check GOA code is entered in SITE_DATA")
BUT what I'd really like to do is combine the FILTER, MID, and FIND functions to be able to pull the relevant data across even if there are multiple trial codes within the trial column in the live data table. This would make the data entry process much more efficient and we could capture data for multiple trial sites at a time. Like this:
=FILTER(OnlineSprayBookAllData[[Date]:[Photo or Video]],(MID(OnlineSprayBookAllData[Trial],(FIND(Table5[@SITE1],OnlineSprayBookAllData[Trial],1)),11))=Table5[@SITE1],"Please check GOA code is entered in SITE_DATA")
As you can see, It does not work. It does work if you select just a single cell in the FIND 'within_text' portion of the function, but not if you select the whole table column. I've had the same lack of success using the INDEX SMALL functions. Is there anyway I can make this work??
Warmly,
Jos.
Hello josiahmay
Could you provide a sample file of yours (w/o sensitive data)? it would be much more helpful if you could also manually put the desired output on another sheet if possible.- Hi, sorry yes, that would have been a better way to do it!
But I've since found an answer on another thread. For anyone that lands here, the key for me was FILTER, ISNUMBER, and SEARCH.
=FILTER(OnlineSprayBookAllData[[Date]:[Photo or Video]], ISNUMBER(SEARCH(Table5[@SITE1], OnlineSprayBookAllData[Trial])), "Please check GOA code is entered in SITE_DATA")