Add new inventory list to existing list
 

I have an existing inventory with the item # and qty listed. When I add new
items to the inventory, I need to have a way to check for duplicates (besides
the Find/Replace functions), and if there are duplicates, add the quantities
together, otherwise add it as a new entry. HELP!

Add new inventory list to existing list
 

The down and dirty way that I would approach this is to have a row at the
top for new inputs
The cell for the Item description would have DataValidation = List (allow
any entry) The list should refer to the column
that contains the Item descriptions where A1 is the cell with the Item
description.
In my example List1 =Sheet!$A:$A

In this way your DataValidation list will contain everything in column A
(including the new item name). You can either choose
an existing description or add a new one.

Create a toolbar button or an on sheet button attached to code.

The code would work something like this:

Dim x As Long
' count number of occurances of item description in column A
x = WorksheetFunction.CountIf(Range("List1"), Range("A1"))

If x = 1 Then ' description only appears in A1
' your code to copy and paste to end of list
' your code to resort the list
ElseIf x = 2 Then ' the description already exists
' your code to find row with matching item
' your code to adjust count on above row
ElseIf x 2 Then ' you have multiple entries
MsgBox ("There are multiple items in list.")
End If


hope this gets you there...
--
steveB

Remove "AYN" from email to respond
"jweasl" wrote in message
...
I have an existing inventory with the item # and qty listed. When I add new
items to the inventory, I need to have a way to check for duplicates
(besides
the Find/Replace functions), and if there are duplicates, add the
quantities
together, otherwise add it as a new entry. HELP!

Add new inventory list to existing list
 

Well that's definitely a start.... I prefer to input all of the data at once,
not one at a time (a bit tedious)... is there a way to condense a list.. ie
item #00001 is listed five times with qty1 each time, make it so item #00001
is listed once with qty 5

"STEVE BELL" wrote:

The down and dirty way that I would approach this is to have a row at the
top for new inputs
The cell for the Item description would have DataValidation = List (allow
any entry) The list should refer to the column
that contains the Item descriptions where A1 is the cell with the Item
description.
In my example List1 =Sheet!$A:$A

In this way your DataValidation list will contain everything in column A
(including the new item name). You can either choose
an existing description or add a new one.

Create a toolbar button or an on sheet button attached to code.

The code would work something like this:

Dim x As Long
' count number of occurances of item description in column A
x = WorksheetFunction.CountIf(Range("List1"), Range("A1"))

If x = 1 Then ' description only appears in A1
' your code to copy and paste to end of list
' your code to resort the list
ElseIf x = 2 Then ' the description already exists
' your code to find row with matching item
' your code to adjust count on above row
ElseIf x 2 Then ' you have multiple entries
MsgBox ("There are multiple items in list.")
End If


hope this gets you there...
--
steveB

Remove "AYN" from email to respond
"jweasl" wrote in message
...
I have an existing inventory with the item # and qty listed. When I add new
items to the inventory, I need to have a way to check for duplicates
(besides
the Find/Replace functions), and if there are duplicates, add the
quantities
together, otherwise add it as a new entry. HELP!

Add new inventory list to existing list
 

If you are working from another list in another workbook, or in the same
workbook:

You could loop through this list and test each line with a countif and than
transfer either a
new record or add the count to existing records.
Than resort at the end of the code.

If you avoid selecting - this should go pretty fast. I have done this with
one or two
thousand new records in a very short time.

You could also do the code in 2 parts.

In the first part condense the new list.
And in the second transfer the condensed list.

If you would like - send me some samples and I'll look at it.


--
steveB

Remove "AYN" from email to respond
"jweasl" wrote in message
...
Well that's definitely a start.... I prefer to input all of the data at
once,
not one at a time (a bit tedious)... is there a way to condense a list..
ie
item #00001 is listed five times with qty1 each time, make it so item
#00001
is listed once with qty 5

"STEVE BELL" wrote:

The down and dirty way that I would approach this is to have a row at the
top for new inputs
The cell for the Item description would have DataValidation = List (allow
any entry) The list should refer to the column
that contains the Item descriptions where A1 is the cell with the Item
description.
In my example List1 =Sheet!$A:$A

In this way your DataValidation list will contain everything in column A
(including the new item name). You can either choose
an existing description or add a new one.

Create a toolbar button or an on sheet button attached to code.

The code would work something like this:

Dim x As Long
' count number of occurances of item description in column A
x = WorksheetFunction.CountIf(Range("List1"), Range("A1"))

If x = 1 Then ' description only appears in A1
' your code to copy and paste to end of list
' your code to resort the list
ElseIf x = 2 Then ' the description already exists
' your code to find row with matching item
' your code to adjust count on above row
ElseIf x 2 Then ' you have multiple entries
MsgBox ("There are multiple items in list.")
End If


hope this gets you there...
--
steveB

Remove "AYN" from email to respond
"jweasl" wrote in message
...
I have an existing inventory with the item # and qty listed. When I add
new
items to the inventory, I need to have a way to check for duplicates
(besides
the Find/Replace functions), and if there are duplicates, add the
quantities
together, otherwise add it as a new entry. HELP!