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?

Report •

✔ Best Answer
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 2
3 - 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 A

The 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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#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.


Report •

#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.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 26, 2017 at 20:01:07
✔ Best Answer
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 2
3 - 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 A

The 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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question