Forum Discussion
Faster Data entry
I am looking for a better way to enter data.
Currently I use CTRL + F for looking up a customer number in a spreadsheet then enter how much of an Item they bought. Example below
| Customer | Item A | Item B | Item C | Item D | Total |
| 100 | 10 | 1 | 11 | ||
| 101 | 9 | 9 | |||
| 102 | 1 | 1 | |||
| 103 | 8 | 3 | 11 | ||
| 104 | 3 | 3 | |||
| 105 | 5 | 5 |
Most customers only buy one type of item at high volumes or mixed items.
The data I enter is out of order therefore I have to search individually.
Is there a faster way to enter the data other than searching every customer number one by one then entering the data into each individual cell?
2 Replies
Sub customer_item_qty_entry() Dim i, j, k As Long i = Application.WorksheetFunction.Match(Cells(12, 1), Range(Cells(1, 1), Cells(7, 1)), 0) j = Application.WorksheetFunction.Match(Cells(12, 2), Range(Cells(1, 1), Cells(1, 5)), 0) Cells(i, j).Value = Cells(i, j).Value + Cells(12, 3).Value End SubMaybe these lines of code are helpful. In the attached file you can select the customer and item from a dropdown in cells A12 and B12 and you can enter the quantity in cell C12. Then you can click the button in cell D10 to add the quantity to the table.
You can try using the CountIFS function.
If you could provide a sample file of the data, I can probably try to be more specific.
