Display a list of names [SOLVED]

[MalR]

Hi guys. I have a worksheet which I use for inputting customer information. It is named “Data”.


On another worksheet (Names) I have a list of existing customer names. There are 13 rows which display Comments, Notes, Surname, First name, Address, Suburb, State, Post code, Phone, Mobile, Email, Car rego, Smoke.


New customers get their details recorded in the Data worksheet in a blank template. I have attached a sample of the template. Currently I have a VLookup in the surname cell which goes to the Names worksheet and brings up certain cells about that customer. I can see if any details have changed and if there are any special customer requirements.


The problem is the VLookup only brings up one surname and there may be several with the same surname. eg there may be six people called “Smith”.


I would like to improve this process with a macro (or perhaps there is a formula) using your help please. You may have a better suggestion but I could include an additional column (column A) with more descriptive surnames eg Smith G&S in my Names worksheet. Perhaps call it “Name”.


Could someone suggest a macro or suggest a formula that when I type in any surname in the format (say Jones B&T) it will go to the Names worksheet for Jones B&T, copy all the data and paste it transverse into my template in the Data worksheet. If no such name exists it will leave the cells blank.


Additional information you will need is: The attached template gets pasted across the spread sheet as shown. There are always 7 columns in the template. When I type a surname it will activate the macro to fill in the missing details. All pasted cells will be in order as displayed in the Names worksheet. (See attachment). The Surname is always in row 8.

Thanks guys

MalR

 

[Nebu]

Hi:

Can you provide the a sample Names tab as well , it would be much comprehend and solve your problem that way.

Thanks

 

[Somendra Misra]

Hi,

This looks like more a database problem, you must create a unique id or key for each person, that way the job will become easy. Why I am saying this is because if there are 6 Smith and you type Smith, on what basis any system will decide which Smith you are looking at, whether you want 3 or 4 or say 1st one?

You Must associate each person with a unique identifier. Ans use it to fetch the data.

Regards,

 

[MalR]

Hi:

Can you provide the a sample Names tab as well , it would be much comprehend and solve your problem that way.

Thanks

I appreciate your help guys. I attach the sample Names worksheet. My Database worksheet (previously attached) has the same setup but runs down the page so the Names data will be copied and pasted transverse into that Database.
Somendra, the target will be surname/initials eg Smith G&S so there should rarely be a duplication conflict. If I see two with the same name I will add an additional letter.
When an existing customer books in again at present I have to find his previous information and copy/paste it into my Database worksheet. I am wondering if by typing say Smith G&S Excel could go to the Names worksheet, pick up all the information for this person and paste it into my Database worksheet. This worksheet contains a lot of other booking information (not shown) about this customer and is the basis of our program.
I realize I could do a Find but this takes time and I want to see all information while the customer is on the phone. In particular in the comments we may say something like "smoked in the room. Never allow back"
Thanks
MalR

 

[Nebu]

Hi:

Do the data tab need infinite no of templates? I guess you want to use the data tab as a front end where you enter your customer data and hit a save button or something so that it will go and sit in your Names tab(DB) which will be your back end. Then when next time when you enter a surname in your data tab if the same person is existing in your DB it will pull all the corresponding info for that particular customer. Then you can make necessary changes for that customer if needed and hit an update button or something it will go and update the respective fields for that customer. Hope I am clear if yes, please let me know your thoughts we can work on it.

Thanks

 

[MalR]

Hi:

Do the data tab need infinite no of templates? I guess you want to use the data tab as a front end where you enter your customer data and hit a save button or something so that it will go and sit in your Names tab(DB) which will be your back end. Then when next time when you enter a surname in your data tab if the same person is existing in your DB it will pull all the corresponding info for that particular customer. Then you can make necessary changes for that customer if needed and hit an update button or something it will go and update the respective fields for that customer. Hope I am clear if yes, please let me know your thoughts we can work on it.

Thanks

Thanks for taking time Nebu. The file "Names macro" is the template. It is 7 columns wide and it goes down for 36 rows. All I do is copy it across the page so the template of 7 columns gets copied numerous times. At the moment it goes to column KPT and when I fill it up to there I will extend it another few hundred columns.
I have shown it duplicated in the attachment to demonstrate how it gets copied across the page. Each template is used for each new booking. I have not included the bottom part because it is only the top section which is important to this project.
Yes it is the front end. Calculates pricing, sets dates, a section gets exported to MYOB for accounting and it connects to Outlook for mailing receipts etc.
What you say is correct and possibly the best way to do it. I can write a macro to go to it and pull it out but I have no idea how to trigger it using the surname. This is why I am going through this forum hoping someone knows how.
If I can get it to work like you say it will save me a lot of time because >80% of our customers are repeat business so the names are already recorded.

 

[Nebu]

Hi:

I am totally tied up for couple of days will get back to you over the weekend.

Thanks

 

[Nebu]

Hi:

Please find the attached.

• Add button for adding a new customer.
• Update button for updating information of an existing customer(eg: change in phone number, address etc)

Note: You will have to give the surname and initials and hit the search button for retrieving the customer info you want to update. After making the necessary changes hit the update button the changes will be saved.

• Delete button for deleting an existing customer, same steps as updating the customer , but here you will hit delete button to remove the customer.
• Clear button to clear the form.

Hope this is what you were looking for, let me know if it helped.

Your data base will be name tab.


Thanks

 

[MalR]

Hi:

Please find the attached.

• Add button for adding a new customer.
• Update button for updating information of an existing customer(eg: change in phone number, address etc)

Note: You will have to give the surname and initials and hit the search button for retrieving the customer info you want to update. After making the necessary changes hit the update button the changes will be saved.

• Delete button for deleting an existing customer, same steps as updating the customer , but here you will hit delete button to remove the customer.
• Clear button to clear the form.

Hope this is what you were looking for, let me know if it helped.

Your data base will be name tab.


Thanks

Thanks greatly for the effort you made Nebu. It will take me some time to put in the names and test it and I will do this when I get a chance. So far it is very impressive and I will get back to you later.
Thanks again
MalR

 

[MalR]

Thanks greatly for the effort you made Nebu. It will take me some time to put in the names and test it and I will do this when I get a chance. So far it is very impressive and I will get back to you later.
Thanks again
MalR

Zebu I tried your macro. A problem I have is that my list of customer names extends for over 3000 rows. If I understand your file I have to input them individually which is not practical.
I had difficulty getting the macro to work to test it. Obviously I did something wrong. I copied the macro correctly but it seems to hang up. If you could provide some steps on how to do it I would be grateful.
Thanks again
MalR

 

[MalR]

Zebu I tried your macro. A problem I have is that my list of customer names extends for over 3000 rows. If I understand your file I have to input them individually which is not practical.
I had difficulty getting the macro to work to test it. Obviously I did something wrong. I copied the macro correctly but it seems to hang up. If you could provide some steps on how to do it I would be grateful.
Thanks again
MalR

Sorry, my typo. I meant to type Nebu!

 

[Nebu]

Hi:

You necessarily do not have to enter all the 3000 entries through the customer form , if you already have a ready made data base you can copy that DB and use it. The customer form can be used to add on to that DB if any new customer gets added .

The steps for testing the macro is the same as what I had given in my earlier post. You don't have to use all the 3000 customers for testing, also make sure that the layout of your DB is same as the file I have written the macro, if the layout of the names tab is different in your test file from that of the macro file I provided then the macro will not work.

If you can post a dummy file with some data where you tried running your test I can look at it and diagnose the problem better.

Thanks

 

[MalR]

Hi:

You necessarily do not have to enter all the 3000 entries through the customer form , if you already have a ready made data base you can copy that DB and use it. The customer form can be used to add on to that DB if any new customer gets added .

The steps for testing the macro is the same as what I had given in my earlier post. You don't have to use all the 3000 customers for testing, also make sure that the layout of your DB is same as the file I have written the macro, if the layout of the names tab is different in your test file from that of the macro file I provided then the macro will not work.

If you can post a dummy file with some data where you tried running your test I can look at it and diagnose the problem better.

Thanks

Nebu I have attached a file with both worksheets in it and with helpful comments written in red in each worksheet. It should answer your questions
Thank you
MalR

 

[Nebu]

Hi:

If the First Name column is separated by only "&" or "and" then use the following formula to add the letters to the name column, there is no need to add it manually

=IF(ISNUMBER(SEARCH("and",B2,1)),A2&LEFT(B2,1)&"&"&MID(B2,SEARCH("and",B2,1)+4,1),A2&LEFT(B2,1)&"&"&MID(B2,SEARCH("&",B2,1)+2,1))

Thanks

 

[MalR]

Wow!! Very clever. Thanks! could you tell me what the name of the formula is so I can research it.
I wonder if you could now test the program please. I enclose the updated file. I obviously have not understood the next steps because I cannot get it to run. I have put some questions in the template worksheet.
Could you tell me please the steps I need to do to put it into my program and to make it work once you are happy with that format
Thanks again
MalR

 

[Nebu]

Hi:

IF(ISNUMBER(SEARCH("and",B2,1)),A2&LEFT(B2,1)&"&"&MID(B2,SEARCH("and",B2,1)+4,1),A2&LEFT(B2,1)&"&"&MID(B2,SEARCH("&",B2,1)+2,1))

The "Search" function search for "and" in the string "First Name" , the search function will return the position of "and", for eg:Bill and Sue the and is starting from the 6th position hence the search will return 6 ."Isnumber" will check for this numeric value if it returns the numeric result it will concatenate as per your requirement, if not it will search for "&" and will concatenate accordingly.

First let me clarify some basics

1. Do the customer form have all the fields you needed to capture?
2. I saw your receipt template do you have all the info populated through your customer form to feed into your receipt template.
3. Are you fine with the format of the customer form? Changing the layout of the form is fairly easy so I asked. I also have put in some smarts for the data entry part for eg: the pin code and phone number have to be entered in a particular format are you happy with it?

I would suggest you to enter some dummy data and see how it go. If you are happy I can set up your background data base in a table format, provided you give me a sample set to customer details the rest of the details you can simply copy and paste into the table.

Note:You will start using the customer form only for adding new customer, not for entering the existing ones, the existing customer info will be a bulk copy and paste.

Thanks

 

[MalR]

Nebu, I enclose the final file that you need. This is what I use

1 Yes this file has all the fields I need to place in the customer list. There are 10 fields

2 Yes the template is set exactly how I use it. The column widths are also set exactly how I use them. Hopefully you do not have to change the column widths.

3. The customer form (the name /address details) will be used exactly like the one in the file. I tell MYOB what fields I want then I export the file from MYOB with just those fields. I only export the fields that I need to use in the template.

When you say you added some smarts. Pin code and phone format. Did you mean to attach a file to show me? If the pin code is a security item there is no need. The program is only used internally and putting in a pin will slow data entry and complicate it.

“give me a sample set to customer details” The customer details are in the Names folder exactly as I import them. They are what we use. They can be used for your “background data base in a table format”

Regards
MalR

 

[Nebu]

Hi:

What I understand from your explanation is that your DB is MYOB and every week you export a data dump from MYOB to your names tab.

You want to retrieve data from names tab based on an ID for example "AdamsB&S" and populate the fields for your receipt template, which you will be sending across to your customer. If that is the case the macro won't be that complex even formulas can achieve this.

Thanks
Nebu

 

[MalR]

That is correct Nebu.

How it works is I have the template. Each customer who books in gets recorded in one template. Their data gets immediately imported into MYOB to do the finance and write the invoices and receipts. So MYOB has a very large data base of names and addresses going for the past 10 years.

The template is also used to produce a booking sheet which is put onto our website so customers can see what rooms are booked and what are available. I have written a macro to do this. To make it easier to understand you can see it on our website oceanviewtouristpark.net.au on the page Availability.

When existing customers ring to book we have to write their names, address, email etc again in the template. But this information is already recorded in the MYOB database.

Each week I can export every person’s information from MYOB . I set the filter to capture only the 10 fields mentioned in my attached files. I create an excel file with this 10 field data

I am hoping that you could create a macro (or a formula would be fine as well) so that I can type in the Surname of the person who rings to book in the template. The macro will go to the list of names, find say “AdamsB&S”, copy the 10 fields associated with that name (ie the first name, address, email etc) and then go back to the template and paste it down the page (transpose). It will act like an auto fill.

Each template is used for one customer so for the next customer I repeat the template.

The new populated fields in the template are then again sent to MYOB and used to create our booking calendar for the web. So it is like a big circle. The process keeps repeating with each new booking.

Because there is much repeat typing I am hoping you can do a formula or macro so that I can populate the 10 fields in the template automatically. This will save me a lot of time because we get numerous bookings each day and perhaps 85% are what we call repeat customers.
regards
MalR

 

[Nebu]

Hi:

Find the attached

Enter the ID all the Fields from the name tab will be automatically populated. I have also incorporated some calculations which made sense to me , you can remove it if you are not happy with it. I have highlighted all the cells with yellow where I have made the changes.

Thanks

 

[MalR]

Thanks Nebu. I have been tied up and will get to it shortly and reply to you
Mal

 

[MalR]

Hi Nebu. I was unable to work on this for a while

Your formulas are working brilliantly and will be saving me a lot of time. Thank you very much!

One last thing please. I need to put the formulas into my Booking program to make it functional. The program is called “Booking program.xlsm”. The worksheet with the names exported from MYOB is called “MYOB names”. The template that creates the addresses by typing the surname is called “Nebu template”.

I tried to rewrite the formula but I get a Ref error (formula) ie I tried

=IFERROR(INDEX('Booking program.xlsm'!Table1[Names],MATCH($D$10,'Booking program.xlsm'!Table1[ID],0),1)," ")

In summary; Inside the file called “Booking program.xlsm” I have a number of worksheets. I need the template to be copied into a worksheet called “Database”. I need to copy many blank templates across the page ready to take customer entries. Each customer who books will have a single entry or card using your formulas by typing the surname. I then do the same for the next one who books into the next template form etc etc.

ie Booking program.xlsm has worksheets MYOB names, Nebu template, Database.

MYOB names gets updated weekly by exporting from MYOB.

Nebu template is the worksheet that has my template in its original form

I copy the template into the worksheet called Database multiple times across the page. All new booking customers get put in here. I type the surname and your formulas fill out the customer’s details if they are in the MYOB names worksheet.

I enclose a file to show this and how your template is now set up.

I hope this is enough information for you?

Thanks very much

MalR

 

[Nebu]

Hi:

I have fixed the formula. I noticed that you have drop downs as data validation from the excel file Booking program.xlsm, if possible you can have a list tab in the file and do all the validation from there. I am not sure why you want to have so may template setup in the Database tab, hope there is valid reason for it.

find the attached.

Note: I have converted the MYOB names tab into a table, Now you can copy and paste the formula as far as you want in the Database sheet.

Thanks

 

[MalR]

Again thanks Nebu. Sorry for the delay. Been busy. I have enclosed a file that answers your questions. I respect that you could have a better way of doing it so I am always thankful to hear your comments.

The worksheet has a list of my drop downs. To answer you, I thought these would be quicker to fill out than using tabs. Also I could be anywhere in the long worksheet. I am not an expert on tabs but if you think this is not correct I am happy to take advice. I have linked the drop downs on one of the templates to make it easier to understand.

I enclose the file with a worksheet with the template copied and completed for several customers. Each customer who rings to book gets put into a new template as shown. I also have included a Display worksheet where most of the data is displayed. We use this sheet as our major viewing sheet for our daily work. This should help you understand why we use many copied templates.

I realize that there must be easier ways of doing this but it works for me, is quick and accurate but always open to suggestions
I have not had a chance to try out your new formulas but will get to it shortly.

I Appreciate your comments

MalR

 

[Nebu]

Hi:

I could not find the DV you had done. I have done a few DV in database tab, The beauty of this is that you have to do DV only for one template , then you can copy and paste this template umpteen times, DV will also get copied. I am attaching a copy or your perusal.

Thanks