SOLVED

Selecting data from one table based on criteria

Copper Contributor

Hello experts,

 

Hope you are well & happy new year!

 

Can you help me with the below?

 

I have a table of large data that requires selected information being pulled out into another table, based on criteria. I have a formula as an array copied down into my filtered table.

 

This works great in pulling out data based on a criteria, but the one thing I cannot do is get the filtered table to add in date format. The reason why is when new lines are added to the master table, it reorders my filtered table that local comments are put next to. This results in data mismatching the correct row.

 

What I want is to keep the filtered table in a consistent flow that stops data becoming disjointed.

 

Please see attached example spreadsheet that should explain better.

 

Any help most appreciated!

 

Thanks

 

Matt

5 Replies

@matt0020190 

Create a separate sheet for the comments and use VLOOKUP to display the comments in the filtered table. See the attached version,

Thanks for the quick reply.

Is there any other way round this? I didnt really want to start complicating by creating another sheet. The idea was to filter for this very reason or could have done it all on the master data sheet in the first place
best response confirmed by matt0020190 (Copper Contributor)
Solution

@matt0020190 

 

Hello, I don´t know if this formula works.

Is doing the same as the formulas that you had but with just a dynamic array formula.

=SORT(FILTER(FILTER(Sheet2!A5:F20,COUNTIF(Example!H2,Sheet2!F5:F20)),{1,1,0,0,1,0}),3,1)

 

The new information is going to be sorted by the date, so the comments are not going to change.

 

Attached is the excel file.

Hi, I had a complete computer crash a couple of weeks ago but it is almost back operating as usual. However, for reasons unknown, I can no longer delete any Excel spreadsheets. Any ideas?
Oops, Sorry Matt, I haven't found how this works yet.
1 best response

Accepted Solutions
best response confirmed by matt0020190 (Copper Contributor)
Solution

@matt0020190 

 

Hello, I don´t know if this formula works.

Is doing the same as the formulas that you had but with just a dynamic array formula.

=SORT(FILTER(FILTER(Sheet2!A5:F20,COUNTIF(Example!H2,Sheet2!F5:F20)),{1,1,0,0,1,0}),3,1)

 

The new information is going to be sorted by the date, so the comments are not going to change.

 

Attached is the excel file.

View solution in original post