Consolidating Rows

Originally Posted by motofabio

My first post after referencing the forums here many, many times. So thanks to everyone for all the solutions I've found in the past! I apologize if my new topic has already been answered, but I can't seem to find it.

I have a workbook with two sheets, "data" and "results". On the data sheet, columns are A: start date, B: end date, C: name, D: product. The data is replaced each day and typically there are 450 rows. About 10 rows per day are new, i.e., have a start date (A) with the same date as today.

On the results sheet, I would like to display ONLY the data from the rows which have a start date of today, in essence producing a list of about 10 each time the new data is dumped.

I know I can just sort the data and look at the top rows, but I'll need to apply this same function to a much more complicated workbook where doing that is not an option, so I need to find the proper function - if it can be done.

Thanks in advance!

Hi,

easiest is probably a Macro, which can be triggered by a Shortcut Key to generate the second sheet as required.

Tools, Macro, Record new Macro, then
Data AutoFilter, dropdown and select today,
select rows 1 to many (say 1000 more than the last used row, to allow for expansion)
Copy and select the second sheet and Paste.

Stop recording, and Edit the macro, Options to add a shortcut key, and edit the macro to replace the filtered item with a parameter, such that

Please Login or Register  to view this content.

Let me know how you go.
---

Originally Posted by motofabio

I was hoping to be able to set this up without a macro.

Ideally I'd use a formula that ignores values if zero and checks the next row for data. You know how VLOOKUP returns the first row's data if the range contains more than one row with the given criteria? Well, I want it to return the first row, then give me the next value on my next row, then the next, etc. I might be asking too much of Excel though.

ok - something like

=IF(ROWS(B$2:B2)<=COUNTIF(Data!$A$2:$A$21,$A$2),INDEX(Data!$B$2:$B$21,SMALL(IF(Data!$A$2:$A$21=$A$2,ROW(Data!$A$2:$A$21)-ROW(Data!$A$2)+1),ROWS(B$2:B2))),"")

should do that (CSE = CTRL/Shift/Enter not just enter) with your date in A2, however your comment about 'zero values' is somewhat vague, you have Start Date, End Date and Product, . . . which one is 'Values' ?

You will need to set the range 2:21 to suit your data.

---