Assistance with reporting data inorder with no blank cells or rows inbetween

[Ghost Of Casper]

Quick rundown.

I have Sheet1 with one data set that covers A1:Y1000, Sheet2 is the second sheet that covers A1:M1000, Sheet3 is a preliminary sort of Sheet1 reducing the data set to cover A1:J1000, Sheet4 A:A has the following formula =IF('Sheet2'!G:G="Conditional",'Sheet2'!A:A,"")

Using the results from Sheet4 A:A I have =IF(ISERROR(VLOOKUP(A:A,Sheet3'!A$2:J$1000,4,FALSE)),"",VLOOKUP(A:A,'Sheet3'!A$2:J$1000,4,FALSE)) returning the requested data in columns C-L in the same row that they appear Sheet3 on Sheet4. My issue now is since I'm pulling from such a large data set there are a lot of empty cells rows between the returned data.

My question, is there a formula that will assist me in taking the results of the formula above and sending that data to a new sheet minus the blank rows? Ideally I would like to stay away from Macros and reuse this workbook with new data and not have to recopy formulas.

I can think of a logic string that would work. The formula would return the first non-blank cell it would see, but also reference any cells above the result cell. If the data is already returned in a cell above it would then move to the next non-blank cell. rinse and repeat for the full data set it is looking at.


Thank you for any assistance with this.

[cheal2]

I personally would move the equations of Sheet 4 to the right one column and put the following in A:A

Please Login or Register  to view this content.

Then in Sheet 5 A:A you do a numerical list 1 to some large number. Then in B:B you can start grabbing your helps rows:

Please Login or Register  to view this content.

Then to start grabbing your data you put the following in C:C

Please Login or Register  to view this content.

You can copy the above formula across and change the "2" above for each column so you grab each unique value of your data as you go. Therefore if you want to grab Sheet4 C:C, you'd change it to 3, 4 gives you D:D etc etc.

Hope this helps.

[FDibbins]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.