# Solved Excel Help: How to create an individual record from a list?

April 26, 2017 at 06:02:02
Specs: Windows 7
 Hello, I am hoping that you can help me with an excel problem. I have a large list of clients who completed tests of various days and each clientâ€™s name appears several times on the list based on them completing a test on a given day. For example, (in Sheet1) John Doe appears in column A, 25 times based on the dates he completes a tests. The date that he completed the test is in column B and, the amount he is charged in Column C, the date he paid for the test in column D and the amount he paid on that date in column E. I have included an example below. What I would like to do is to automatically create a record for each client in Sheet2, where the name of each client would appear only one time. So, each time a new client who completes a test is entered into Sheet1, their name would appear is column A (only one time), of Sheet2 and then I could do some formulas to keep a running balance for each client. Any help you can provide would be greatly appreciated. ```Name DateTested Charge DatePaid AmountPaid Tina Doe 4/24/2017 \$10.00 4/24/2017 \$5.00 Tina Doe 4/25/2017 \$10.00 4/25/2017 \$5.00 James Does 3/1/2017 \$10.00 James Does 3/3/2017 \$10.00 John Doe 4/15/2017 \$10.00 4/15/2017 \$10.00 John Doe 4/17/2017 \$10.00 4/17/2017 \$5.00 Jane Doe 3/15/2017 \$10.00 Jane Doe 3/20/2017 \$10.00 Tina Doe 4/26/2017 \$10.00 4/26/2017 \$2.00 John Doe 3/22/2017 \$10.00 3/22/2017 \$3.00 Tina Doe 4/27/2017 \$10.00 ```

See More: Excel Help: How to create an individual record from a list?

April 26, 2017 at 20:01:07
 1 - Follow Mike's suggestion with your existing list to create a list with no duplicates in Sheet2 Column A.2 - Enter the formulas you plan to use on Sheet 23 - Right-click on the sheet tab for Sheet1 and choose View Code.4 - Paste the following code into the pane that opens.5 - Enter a name anywhere in Sheet1 Column AThe code will search Sheet2 Column A for the name that was just entered. If it doesn't find it, it will copy the last row on Sheet 2 to the next row and place the new name in Column A.```Private Sub Worksheet_Change(ByVal Target As Range) 'Determine if change was made to Column A If Target.Cells.Count = 1 And Target.Column = 1 Then With Sheets(2).Columns(1) 'Search for new name in Sheet(2)Column A Set n = .Find(Target, lookat:=xlWhole) 'If not found, add new row for new name If n Is Nothing Then lastRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row Sheets(2).Rows(lastRw).EntireRow.Copy _ Sheets(2).Cells(lastRw + 1, 1) Sheets(2).Cells(lastRw + 1, 1) = Target End If End With End If End Sub```message edited by DerbyDad03

#1
April 26, 2017 at 08:03:20
 Simplest way I can think of is to Copy all of Column A, your Name column, from Sheet 1 over to Sheet 2,then, on the Ribbon, under the Data Tab, use the Remove Duplicates.That gives you your list of names on Sheet 2, you can then use a lookup to get any other data you need.What I would like to do is to automatically create a record for each client in Sheet2, where the name of each client would appear only one time.For this you will need a Macro, and unfortunately my macro skills are just above nil.MIKEmessage edited by mmcconaghy

#2
April 26, 2017 at 12:00:31
 Thank you Mike, your suggestion is good an would work. The only challenge is that every time a new client is entered for a test, having not previously completed a test, it would require to repeat the steps you outlined. And if they were to simply add the name of the person to the list on Sheet 2 and misspelled something, it would create another issue. I am hoping to find a way to make this work. I would think given some of the large list of payment records that people have out there that there is some way to make this work. Thank you for your help.

#3
April 26, 2017 at 16:36:49
 To get it to work like you want, you will need to utilize a macro, which I'm unable to supply.Hopefully some one with more Macro skills will offer a suggestion.MIKEhttp://www.skeptic.com/

 1 - Follow Mike's suggestion with your existing list to create a list with no duplicates in Sheet2 Column A.2 - Enter the formulas you plan to use on Sheet 23 - Right-click on the sheet tab for Sheet1 and choose View Code.4 - Paste the following code into the pane that opens.5 - Enter a name anywhere in Sheet1 Column AThe code will search Sheet2 Column A for the name that was just entered. If it doesn't find it, it will copy the last row on Sheet 2 to the next row and place the new name in Column A.```Private Sub Worksheet_Change(ByVal Target As Range) 'Determine if change was made to Column A If Target.Cells.Count = 1 And Target.Column = 1 Then With Sheets(2).Columns(1) 'Search for new name in Sheet(2)Column A Set n = .Find(Target, lookat:=xlWhole) 'If not found, add new row for new name If n Is Nothing Then lastRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row Sheets(2).Rows(lastRw).EntireRow.Copy _ Sheets(2).Cells(lastRw + 1, 1) Sheets(2).Cells(lastRw + 1, 1) = Target End If End With End If End Sub```message edited by DerbyDad03