Cell formula for adding a second list of products to the bottom of the first variable list

[Peter Davison]

I have a list of products A35 to A300 on Sheet 1
I have a second list of products A35 to A50 on Sheet 2
I want to add the second list one by one to the bottom of the list on sheet 1, but the end of sheet 1 may be 300 or could be 303 or 306 etc.
Is there a cell formula that can achieve this?
I thought it might be an If with a count etc.

As always any help you can give would be great.
Thanks

 

[aRandomHelper]

I want to add the second list one by one to the bottom of the list on sheet 1

How do you determine which ones to add to sheet 1?

What's your process like? Sheet 2 is imported data from somewhere else, then you choose which to add to Sheet 1?

You may need a VBA solution instead.

 

[Peter Davison]

Thanks for getting back to me.
How do you determine which ones to add to sheet 1? - What ever order the second list is in it is just to add to the bottom of the first list
What's your process like? Sheet 2 is imported data from somewhere else, then you choose which to add to Sheet 1? - It is just data created from formulas within the workbook

It basically just need to find the last forularised cell that has data (formularised cells without data have a "z" in them for other reasons) then add the second list to it (which always starts in the same cells and is also formularised

Hope the answers help
Thankyou.

 

[aRandomHelper]

So if I understood correctly, you want to find the starting cell "without data" (i.e. showing "z") in Sheet 1, then place those formulas from Sheet 2 into there in Sheet 1?

Maybe I am missing something, it sounds like you don't need anything special, except to just copy the cells from Sheet 2, and paste into Sheet 1.
Does this happen very often? Are you trying to automate it?

It is just data created from formulas within the workbook

Maybe show the formulas you're working with, and possibly show what the result sheet you're trying to achieve looks like?

 

[Peter Davison]

The current list in Sheet 1 is 530 rows, but current data at anytime can be in any number of rows (e.g. 275 - rows 276 to 530 will have a "z" in them).
I just want to be able to show the list on sheet 2, which again will be variable and start it at row 276.

Hope this explains okay

 

[aRandomHelper]

Understood slightly better now. Can you show your working formulas for both Sheets 1 and 2? Might be able to adapt something. Use XL2BB plugin to easily show it.

 

[Peter Davison]

I can show you below, but I had to remove XL2BB from my laptop because it was affecting a file that I had. (Not sure why). I think it might be because I have 4 workbooks all linking together.
I mentioned a "z" before I meant a "-" apologies
Formula for the first sheet is -

=IF($A35="-","-",VLOOKUP($A35,'C:\Cluster Insight\[Breakfast Foods Combined Raw Data.xlsm]Product Data'!$A$2:$AZ$500,2,FALSE))

Formula for the second sheet is -
=IFERROR(IF($A35="-","-",VLOOKUP($A35,'C:\Cluster Insight\[Breakfast Foods Combined Raw Data.xlsm]New Products'!$A:$Z,MATCH(B$34,'C:\Cluster Insight\[Breakfast Foods Combined Raw Data.xlsm]New Products'!$A$1:$Z$1,0),FALSE)),"")

The final list I want will be in a new third worksheet
Hope this is helpful.
and thank you for your patience.

 

[aRandomHelper]

The final list I want will be in a new third worksheet

Is there already something set up on this third worksheet?

Anyhow, since you're using Office 365, I suggest looking into the FILTER function, which I think can do what you want.

Assuming the formulas are in column B, try using something like this on the third worksheet:

=FILTER(CHOOSE({1,2},Sheet1!B:B,Sheet2!B:B),CHOOSE({1,2},Sheet1!B:B,Sheet2!B:B)<>"-","")

I can't test this since I do not have 365.

 

[Peter Davison]

Okay thanks I will give this a test after my meetings today.
I really appreciate your time.

 

[Peter Davison]

I did try it but I couldn't get it to work, however I have managed a work around. Not exactly what I wanted but working okay.
Thanks again for all your help. I really appreciate it.