Consolidate Lists from Multiple Sheets on another Sheet
 

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

Consolidate Lists from Multiple Sheets on another Sheet
 

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" wrote:

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

Consolidate Lists from Multiple Sheets on another Sheet
 

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" wrote:

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" wrote:

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

Consolidate Lists from Multiple Sheets on another Sheet
 

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" wrote:

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" wrote:

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" wrote:

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

Consolidate Lists from Multiple Sheets on another Sheet
 

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

Consolidate Lists from Multiple Sheets on another Sheet
 

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" wrote:

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?

Consolidate Lists from Multiple Sheets on another Sheet
 

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

"Sheeloo" wrote:

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" wrote:

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?

Consolidate Lists from Multiple Sheets on another Sheet
 

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" wrote:

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

"Sheeloo" wrote:

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" wrote:

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?

Consolidate Lists from Multiple Sheets on another Sheet
 

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

"Sheeloo" wrote:

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" wrote:

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

"Sheeloo" wrote:

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" wrote:

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?

Consolidate Lists from Multiple Sheets on another Sheet
 

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.

"Sheeloo" wrote:

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" wrote:

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

"Sheeloo" wrote:

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" wrote:

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?