Forum Discussion
Excel formulaqs and functions
SergeiBaklan Thx Sergio. Might there be a way to merge all the rows with data and w/o data(blanks) so that I only have rows with the data I want and not multiple rows of blanks?? The Filter function does it, but doesn't seem to retain/save the merged/filtered data (w/o blanks)nor permit additional calculations while in the filter mode. Thx for your help.
You may use INDEX/MATCH with criteria, but I didn't catch what exactly do you compare. If you may submit small sample file without sensitive information that will be easier.
Thanks Sergio for your offer of assistance. I've attached a sample file.
The Master file contains the daily egg collections by egg # and pen#.
The sub file A1 pulls the data from the Master file from Pen A1 into the A1 file, which works as you can see.
The problem is that there will be many blank cells (2-5,000 rows) where the egg is not from pen A1, but where the data will go to other sub files (ie A2,A3 etc). The filter function seems to only work with data already in A1, but not for new egg data generated, so I'm trying to find a formula to sequentially put new data in the next row in A1(to consolidate the data in A1), rather than have all the blank rows of data in between each row.
Your thoughts/suggestions will be greatly appreciated.
Thx again.
If to locate result as
you may add to D5 formula
=IFERROR(INDEX(Master!$A$2:$A$20000,AGGREGATE(15,6,1/(D$4=Master!$B$2:$B$20000)*(ROW(Master!$B$2:$B$20000)-ROW(Master!$B$1)),(ROW()-ROW($D$4)))),"")drag it to the right and when entire row (D5:E5 in this case) down till empty cells appear. Or more for future expanding of the source data.
Using of absolute/relative and proper references is critical.
Hi Sergei,
I copied your formula into cell D5 and dragged formula over to E5 cell and down the rows.
Attached is my result (all zeros), unlike your result. What am I doing wrong? Many thanks.