Solved VBA in excel which will display,add and count unique values

July 24, 2016 at 15:55:48
Specs: Windows 64
Hi forum!

I am trying to create VBA in excel that will display the unique values of customers in sheet General, column A. all information about current customers it will take a leaf out of Sheet1, and when you add a new customer and the corresponding amounts, these values will be automatically added to the General sheet and count the total amounts for a particular client in columns payment / exposed /debt. should automatically pick up new information introduced in Sheet1 and display it in sheet General.

For example:

Sheet1:

A B C

1 Amount of account Clients Paid
2
3 123 Client1 23
4 329 Client2
1000 Client3 10
453 Client1 400
321 Client2 34
569 Client3 121
.. Client4
.. Client4

infinity infinity


General:

A B C D

1 Clients Payment Exposed Debt

2 Client1 423 576 153
3 Client2
Client3
Client4
..
..

Please, help me!!!!!


See More: VBA in excel which will display,add and count unique values

Report •


✔ Best Answer
July 27, 2016 at 11:57:32
Try this code.

I suggest that you try this in a back-up copy of your workbook in case things go terribly wrong.

Sub ConsolidateDebt()
Dim lastL_Rw, lastG_Rw

'Clear Existing Data From General, Add Column Headings
   With Sheets("General")
      .Cells.ClearContents
      .Range("B1") = "Payment"
      .Range("C1") = "Exposed"
      .Range("D1") = "Debt"
   End With
   
'Determine Last Row With Data In List1 Column B (Clients)
   lastL_Rw = Sheets("List1").Range("B" & Rows.Count).End(xlUp).Row

'Create Filtered List Of Clients On General
   Sheets("List1").Range("B1:B" & lastL_Rw).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Sheets("General").Range("A1"), Unique:=True
        
'Determine Last Row With Data In General Column A (Clients)
   lastG_Rw = Sheets("General").Range("A" & Rows.Count).End(xlUp).Row

'Insert Formulas On General
    With Sheets("General")
        .Range("B2:B" & lastG_Rw).Formula = _
            "=SUMIF(List1!B2:B" & lastL_Rw & ",General!A2,List1!C2:C" & lastL_Rw & ")"

        .Range("C2:C" & lastG_Rw).Formula = _
            "=SUMIF(List1!B2:B" & lastL_Rw & ",General!A2,List1!A2:C" & lastL_Rw & ")"
            
        .Range("D2:D" & lastG_Rw).Formula = _
            "=C2-B2"
    End With
End Sub

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



#1
July 24, 2016 at 18:06:16
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

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


Report •

#2
July 25, 2016 at 11:50:00
Apologies - I think this format will work out better:

I am trying to create VBA in excel that will display the unique values of
customers in sheet General, column A. all information about current
customers it will take a leaf out of List1, and when you add a new
customer and the corresponding amounts, these values will be
automatically added to the General sheet and count the total amounts
for a particular client in columns payment / exposed /debt. should
automatically pick up new information introduced in List1 and display it
in sheet General.

For example:

List1:

	   A	           B	       C
1   Amount of account	Clients      Paid	
2
3         100           Client1       20
4         300           Client2	
5        1000           Client3       10
6         450           Client1      400
7         350           Client2       30
8         500           Client3      100
9          ..           Client4 
10         ..           Client4 
        infinity        infinity	

General:

         A            B         C          D
1      Clients     Payment    Exposed    Debt
2      Client1        420      550       130
3      Client2         30      650       620
4      Client3 
5      Client4 

Please, help me!!!!!


Report •

#3
July 25, 2016 at 12:42:23
I've edited your post to show what the format should look like. You don't need to apply the pre-tags to each individual line, you apply them to a block of text, like an entire table.

I will work on your question.

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


Report •

Related Solutions

#4
July 26, 2016 at 07:39:40
Thank you, that you are trying to help me

Report •

#5
July 27, 2016 at 11:57:32
✔ Best Answer
Try this code.

I suggest that you try this in a back-up copy of your workbook in case things go terribly wrong.

Sub ConsolidateDebt()
Dim lastL_Rw, lastG_Rw

'Clear Existing Data From General, Add Column Headings
   With Sheets("General")
      .Cells.ClearContents
      .Range("B1") = "Payment"
      .Range("C1") = "Exposed"
      .Range("D1") = "Debt"
   End With
   
'Determine Last Row With Data In List1 Column B (Clients)
   lastL_Rw = Sheets("List1").Range("B" & Rows.Count).End(xlUp).Row

'Create Filtered List Of Clients On General
   Sheets("List1").Range("B1:B" & lastL_Rw).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Sheets("General").Range("A1"), Unique:=True
        
'Determine Last Row With Data In General Column A (Clients)
   lastG_Rw = Sheets("General").Range("A" & Rows.Count).End(xlUp).Row

'Insert Formulas On General
    With Sheets("General")
        .Range("B2:B" & lastG_Rw).Formula = _
            "=SUMIF(List1!B2:B" & lastL_Rw & ",General!A2,List1!C2:C" & lastL_Rw & ")"

        .Range("C2:C" & lastG_Rw).Formula = _
            "=SUMIF(List1!B2:B" & lastL_Rw & ",General!A2,List1!A2:C" & lastL_Rw & ")"
            
        .Range("D2:D" & lastG_Rw).Formula = _
            "=C2-B2"
    End With
End Sub

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


Report •

#6
July 27, 2016 at 13:13:24
DerbyDad03: Thaaaaank you very much!!! thank you for your help!!! thank you!!!

Report •

#7
July 27, 2016 at 15:24:11
I'm glad it worked out for you. Please note that the code is not "automatic" as you requested. It needs to be run manually each time you make a change.

It could be automated, but I would need to know how that automation should be triggered. Simply saying "whenever a new client is added" is probably not enough information.

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


Report •

Ask Question