Forum Discussion
Populating a new table with data that match criteria from another table.
Hello! I have a table with some clients, model for equipment and date of next maintenance, and I need to re order those in another table, the clients that have maintenance in the first semester and the second.
I need the new table to tell me the Name, model and month.
I have this test sheet to play around with the formulas or whatever.
I pulled the Client and Model from another sheet, the date is:
=IF(Equipos!D2="v";DATE.MONTH(Equipos!Q2;6);DATE.MONTH(Equipos!Q2;12))
(the v is for veterinary, every 6 months, and normal is every 12 months. Not important for the question but I guess is better you know why is there) Also, how could I just put a blank space if I don't have a date in Equipos!Q2 ??
And the MONTH and SEMESTER is just to have it there if it helps to make the table I need, lol. Month being
=TEXT(F5;"mmmm")
and semester being
=IF(AND(MONTH(F5)<=6;YEAR(F5)=2024);"1st";"2nd")
I know how to pull the client and model, obviously, as I already did it, haha, but I don't know how to pull just the ones that I need.
Also, please don't mind the colors of DATE, as I don't remember why I was doing it.
Also, I need that it changes the color of the data on the new tables, red if it's due in the month, yellow if is the next month. And maybe another color if it was due last month and we didn't made the maintenance.
Thank you very much!
4 Replies
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 approachFormula 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.
