Consolidate Lists from Multiple Sheets on another Sheet

[Lemmesee]

I have 2 sheets with Item Numbers, Quantities, And Descritpions.
The Description of the Item May be 1 row or Multiple rows.

On a third sheet, I need to consolidate the list from "sheet 1" and "Sheet
2" elimating the Item Number and Quantity if the Description is more than 1
Line.

Note:
On the First 2 Sheets I have a formula for the Item Number, similar to:
=IF(B2="","",MAX($A$2:A10)+1) , So that once a QNTY is entered in Column "B"
it gives a new Item Number. Allowing "Blank" rows and/or "Multi-Line
Descriptions".

I am not sure if you can make sense of this example below, But If you can I
would appreciate it.
Thanks in advance.


"Sheet 1"
A B C
1 Item# QNTY Description
2 1 10 Apple
3 Red
4
5 2 11 Apple
6 Green
7
8
9
10 3 1 Orange

"Sheet 2"
A B C
1 Item# QNTY Description
2 4 2 Orange
3 Rotten
4
5 5 1 Banana

“Sheet 3â€
A B C
1 Item# QNTY Description
2 1 10 Apple
3 Red
4 2 11 Apple
5 Green
6 3 1 Orange
7 4 2 Orange
8 Rotten
9 5 1 Banana

 

[Sheeloo]

Dear Let Me See

Sorry, your problem is not clear... Your example does not have same item on
both sheets. How will you treat this case?

Also how do you order the item names?

What you should do i have a helper column which gives the sequence number
within a group. Once you have that it is easy to consolidate, merger, sort
etc...

To me it looks like that you want to merge the two sheets and if the same
item is on both sheets have the item no. only once by eliminating the
quantity with only one of them....

 

[Lemmesee]

I knew that I could not explain it right. So let me try again.
On the first and/or second sheet, I enter a Quantity, The Item# is then
automatically generated(as described in my original post).
Then I add a description, the description of this ITEM may be One line or
multiple Lines.
Then I enter another QNTY, at least, one row below the final row that
contains the description of the previous Item. That said, there may be blank
rows between the various Items.
Now on the third sheet it will be a consolidated list, omitting the empty
rows, ordered by the ITEM#.
Does this make better sense?

 

[Sheeloo]

It helped...

What I don't understand is how you get item# 4 in sheet 2? If you have
formula as described, both sheets will have items 1,2,3,...

I think you want to get all items from sheet 1 and sheet 2 on sheet 3
but I do not understand how you want them numbered?

Can you send the file to me?

 

[Lemmesee]

The Item# 4 gets there by a variation of the formula like this
=IF(B9="","",MAX('Purchase Order'!A23:A35)+1).
I need them numbered in order based on the ITEM#.

And yes I can send the file to you, but where do i send it?

 

[Sheeloo]

Click on my name and you will see the id... make obvious correction and send
the file to me...

Can I have all the items from sheet 1 first and then all from sheet 2 and
then renumber?
You can explain in the mail.

 

[Lemmesee]

I sent the File. Did you get it, or did you give up?

 

[Sheeloo]

No, I did not get it or give up

You have to remove deletethis. from my id... just trying to avoid spammers...

or put this in an Excel cell to get the id

="to_sheeloo" & CHAR(64) & "hotmail.com"

 

[Lemmesee]

Sorry, my bad. I removed the "to_" as well.
I just resent it to you.

 

[Lemmesee]

i have seen some of your posts and I see some pretty awsome macros that you
wrote and I think that my issue may require one as well.