Help to understand how to dynamically extra data with reordering

[0kuden]

Hello,

I am looking for your help in figuring out if it is indeed a macro I need to come up with for the following problem, or if a set of formulas would suffice (which I doubt).

My idea is to summarize in the top of the sheet, in a small formatted section, the 10 oldest entries (from a set of data below) for which the variable is X in a specific column. This would be ordered from oldest to most recent. Once the variable is changed to Y, this entry would not appear anymore in this summary section up top and the latter would reorder itself to then start with the "newest" old entry, and repopulate the 10th row.

Example below is a similar data set, with 7 entries to shorten the example:

[TABLE="width: 858"]
<tbody>[TR]
[TD="class: xl66, width: 143"]Date[/TD]
[TD="class: xl66, width: 143"]Name[/TD]
[TD="class: xl66, width: 143"]Color[/TD]
[TD="class: xl66, width: 143"]Make[/TD]
[TD="class: xl66, width: 143"]Amount[/TD]
[TD="class: xl66, width: 143"]Has replied[/TD]
[/TR]
[TR]
[TD="class: xl65"]5-Jun[/TD]
[TD="class: xl64"]John[/TD]
[TD="class: xl64"]Red[/TD]
[TD="class: xl64"]Alpha[/TD]
[TD="class: xl64"]54[/TD]
[TD="class: xl64"]No[/TD]
[/TR]
[TR]
[TD="class: xl65"]9-May[/TD]
[TD="class: xl64"]Mary[/TD]
[TD="class: xl64"]Blue[/TD]
[TD="class: xl64"]Beta[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl64"]No[/TD]
[/TR]
[TR]
[TD="class: xl65"]11-Dec[/TD]
[TD="class: xl64"]Alfie[/TD]
[TD="class: xl64"]Red[/TD]
[TD="class: xl64"]Charlie[/TD]
[TD="class: xl64"]54[/TD]
[TD="class: xl64"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl65"]17-Jul[/TD]
[TD="class: xl64"]Ronald[/TD]
[TD="class: xl64"]Green[/TD]
[TD="class: xl64"]Alpha[/TD]
[TD="class: xl64"]845[/TD]
[TD="class: xl64"]No[/TD]
[/TR]
[TR]
[TD="class: xl65"]19-Jul[/TD]
[TD="class: xl64"]Paul[/TD]
[TD="class: xl64"]Red[/TD]
[TD="class: xl64"]Beta[/TD]
[TD="class: xl64"]654[/TD]
[TD="class: xl64"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl65"]10-Nov[/TD]
[TD="class: xl64"]Julia[/TD]
[TD="class: xl64"]Red[/TD]
[TD="class: xl64"]Charlie[/TD]
[TD="class: xl64"]125[/TD]
[TD="class: xl64"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl65"]1-Dec[/TD]
[TD="class: xl64"]Betty[/TD]
[TD="class: xl64"]Purple[/TD]
[TD="class: xl64"]Alpha[/TD]
[TD="class: xl64"]5558[/TD]
[TD="class: xl64"]No[/TD]
[/TR]
</tbody>[/TABLE]

And example below is a seperate section where I want this data recalled, with top 3 from oldest to newest, where "no" is the defining variable:

[TABLE="width: 572"]
<tbody>[TR]
[TD="class: xl67, width: 143"]Date[/TD]
[TD="class: xl67, width: 143"]Name[/TD]
[TD="class: xl67, width: 143"]Amount[/TD]
[/TR]
[TR]
[TD="class: xl66"]9-May[/TD]
[TD="class: xl65"]Mary[/TD]
[TD="class: xl65"]100[/TD]
[/TR]
[TR]
[TD="class: xl66"]5-Jun[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl65"]54[/TD]
[/TR]
[TR]
[TD="class: xl66"]17-Jul[/TD]
[TD="class: xl65"]Ronald[/TD]
[TD="class: xl65"]845[/TD]
[/TR]
</tbody>[/TABLE]

The idea is therefore to populate this summary section only if the "Has replied" equals "no". Once i change to a "yes" in the main data section, all fields refresh in the summary section, with the new addition(s) up to specified max number of rows. Manual refresh (button) is not an issue.

Would this work with formulas? Or are we talking intricate coding here to get this done, if at all possible? And if this could work, how would adding new entries with older dates mess up the whole thing?

Sorry if this is not the appropriate place for such a query.

Many many thanks.
0kuden

 

[thisoldman]

Yes, it can be done with formulas. It might be easiest to do with formulas by converting the data to a "structured table." However, you can extract the desired data very, very quickly with Excel's built-in filtering tools.

If your table is structured as you posted, with no completely blank rows and with column header titles—

Select any cell in the data. On the ribbon, select Data >> Filter, the big funnel icon. Dropdown indicators will appear in the header cells.

Click the dropdown indicator in the "Has Replied" column. In the bottom pane of the pop-up menu, clear the "(Select All)" checkbox and place a check in the box next to "No".

The rows where "Has Replied" = "Yes" are now hidden.

Next, click the dropdown in the "Date" header cell and select "Sort Oldest to Newest". The oldest dates are put on top; the oldest ten are your first ten rows.

You can select the data you want, copy it, and when you paste, the hidden cells are not included in the copy+paste operation.

You can clear the filters individually. When you want to see the unfiltered data again, go back to the ribbon and click on the big Filter icon to deselect filtering.

Hope this helps.