Invoicing through excel

[Kagz01]

I have working formulas to pull data and price from database per upc code that has been scanned and entered into the cell.

What is happenning is when i scan a barcode more than once i have multiple lines with the same information. What i would like for excel to do is instead of having multiple line with the same product, delete the multiples and add to quantity colum.

Item. Quantity -----> Item. Quantity
1. 1. 1. 2
1. 1. 2. 3
2. 2
2. 2
2. 2

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Scan​

[/td][td="bgcolor:#F3F3F3"]

Qty​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]1. 1. 1. 2[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCFFCC"]B2: =IF(COUNTIF(A$1:A2, A2) > 1, "", COUNTIF($A$1:$A$6, A2))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]1. 1. 2. 3[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]2. 2[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]2. 2[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]2. 2[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[/table]

 

[Kagz01]

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD="bgcolor: #F3F3F3"]

Scan​

[/TD]
[TD="bgcolor: #F3F3F3"]

Qty​

[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]1. 1. 1. 2[/TD]
[TD="bgcolor: #CCFFCC"]

1​

[/TD]
[TD="bgcolor: #CCFFCC"]B2: =IF(COUNTIF(A$1:A2, A2) > 1, "", COUNTIF($A$1:$A$6, A2))[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]1. 1. 2. 3[/TD]
[TD="bgcolor: #CCFFCC"]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]2. 2[/TD]
[TD="bgcolor: #CCFFCC"]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD]2. 2[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

6​

[/TD]
[TD]2. 2[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

shg, I appreciate the quick reply! However the provided solution doesnt solve my issue. Im not even sure it can be done but im trying hard to find out.

If a second or subsequent cell is entered with the same information, I want that cell to clear and add to the qty. each item will have its own row

Item QTY into this ----> ITEM QTY
1 1 1 4
1 1 2 2
1 1 3 4
1 1
2 1
2 1
3 1
3 1
3 1
3 1

 

[Kagz01]

im sorry its changing it

FROM:
ITEM QTY
1 1
1 1
1 1
2 2
2 2
3 3
3 3

TO:
ITEM QTY
1 3
2 2
3 2

 

[Kagz01]

i hope this works...
This is what it currently looks like..........................................This is what i want excell to do with the information
ITEM........QTY..............................................................................ITEM.............QTY
1...............1.................................................................................1..................2
1...............1.................................................................................2..................3
2...............1......................................................................................................
2...............1......................................................................................................
2...............1......................................................................................................

 

[shg]

It puts the quantity at the first appearance of the item,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Item​

[/td][td="bgcolor:#F3F3F3"]

Qty​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

2​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td="bgcolor:#CCFFCC"]B2: =IF(COUNTIF(A$1:A2, A2) > 1, "", COUNTIF(A:A, A2))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

8​

[/td][td="bgcolor:#CCFFCC"]

4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

2​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

3​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

7​

[/td][td="bgcolor:#CCFFCC"]

6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

8​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

8​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

7​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

10​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

8​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

10​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

4​

[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

9​

[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

7​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

6​

[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

3​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

1​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

7​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

7​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

9​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

9​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

1​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]

7​

[/td][td="bgcolor:#CCFFCC"][/td][td][/td][/tr]
[/table]

You can filter to hide the null strings in col B.

 

[Kagz01]

That will be perfect I can reference the cells on my main sheet and it will keep track of everything. Can I get excel to hide a row that does not have a value in column B Or a value less than one?

 

[shg]

Select those two columns, Data > Filter, click the dropdown in B2, follow your nose ...