| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Consolidate multiple lists, with auto-update | Excel Discussion (Misc queries) | |||
| Consolidate multiple worksheets into new sheet | Excel Worksheet Functions | |||
| Consolidate data from multiple sheets | Excel Worksheet Functions | |||
| how do i use import to consolidate data from multiple sheets | Excel Worksheet Functions | |||
| Autofilter Lists across Multiple Sheets, Maintain Correct Referenc | Excel Worksheet Functions | |||
Linear Mode
