Question regarding worksheet design with customers: How to make them unique?

maria90

New Member
Joined
Apr 9, 2012
Messages
38
Hello

My name is Maria and I'm new to this forum. I have used Excel for a while and I started in August last year with a new, big project for the company I work for. To make you understand my problem, I'd like you to explain what we do: We offer a home delivery service and employ several messengers who deliver the customer's purchase (from a shop) home.

I have a sheet containing the daily orders/deliveries, and one containing the customer's data. For statistical purposes, I let Excel calculate the number of orders, the date of the first order and so on.

1zl4xao.jpg


We have more and more customers and there may be more than one, lets say "Furrer Rina".

Here is my question: How do I make sure that Excel calculates the purchase/data of the correct "Furrer Rina".

Should I make Excel check for the customer's name AND the address, So far Excel checks for the customer's name with a COUTIF formula only. How would I have to change that?


OR

should I create a column with a unique ID? As I use userforms to add most of the data, I' would then have to let Excel create a new number.
Do you suggest using a random number? That would be easy on a userform. But if I were to add a new customer directly on a worksheet, how would I do that?

My second question is about the workbook: Should I create one Excel workbook for all data or one workbook for each year? What/How would you do that? What do you suggest?

I may post other questions in the future, should I encounter any problems. I will then add a link to this question.

Thank you so much in advance for your help.

Maria
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Use the Unique ID. Random numbers could still give you duplicates. Sequential number would be good. To add a customer directly to the Customer Master sheet, go to the next available row and enter data in appropriate cells.

One workbook covering all years makes summarizing and analyzing and researching much easier. Make sure every transaction has a complete data entered.
 
Upvote 0
I would just like to endorse what RonB1111 has said.

A unique ID is definitely the way to go. That's the way your bank does it, and every other organisation / company that you have some kind of account with.
It also allows you the flexibility, if you want, to let some customers have more than one account, which you might want to do.

Second, one workbook covering all your data is ABSOLUTELY the right way to go.
And not just one workbook, but one workSHEET. There are so many threads on this board from people who have NOT done this, and now need help sorting their data which is spread across separate workbooks and worksheets for each year / each month / each day / each employee / each department and so on.
I often advise them to merge their data into a single sheet if at all possible, and for them it is frequently no longer possible because their data is so tangled up.
You're lucky, you're just starting out, and you can still make the right choice.
The right choice is a single workSHEET for ALL your data, and then use Excel's various analysis tools to pull out the bits you want, when you want. There will be all kinds of analysis that you might want to run in the future, that maybe you can't imagine now, and having a single source of data will make this all so much easier for you in the future.

Good luck !
 
Upvote 0
Hi guys

Thanks a lot for your feedback. I will give it a try with the Unique ID and with the sequential number.

I will get back to this forum if I have questions or encounter problems with my project.

Thanks and have a nice week :)

Maria
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top