Modification in Excel Automation

fairchance

fairchance

New member
Joined
Jan 4, 2015
Messages
48
Reaction score
0
Points
0
Dear All

I have a solution for moving a copy of transactions in relevant sheet based on column "A". If the id is single then:
it is moving to 1_referral sheet
if 2 same IDs then record is moving to 2_referral
if 3 same IDs then record is moving to 3_referral
if 4 same IDs then record is moving to 4_referral
if 5 same IDs then record is moving to 5_referral
if 6 or greater than 6 number of same IDs then record is moving to 6_referral

I want to implement the same on Column "K" (Referred By) If it is unique name then it will goes to 1_Referral if double then it goes to 3_referral and so on
More than six similar names will also be shifted to 6_Referral.

here is the worksheet
https://www.dropbox.com/s/wcllb21en8n5wyq/Kopie van Referral Program Tracking(jp) (1).xlsm?dl=0

Kind Regards

Shehbaz H.
 
Your macro does not correctly include in sheet 6 Referral the IDs where there are more than 6 (there are 8 of ID 10752536).
I've added a new Macro (UpdateY) which handles both types (ID and Referred By). I've added 6 new sheets to take the new data.
See attached which contains said macro:
Code: 
Sub UpdateY()
Range("A2:Q" & Application.Max(2, Range("A" & Rows.Count).End(xlUp).Row)).ClearContents
xx = CLng(Left(ActiveSheet.Name, 1))
Select Case LCase(Right(ActiveSheet.Name, 8))
  Case "referral": ofset = 0
  Case "ferredby": ofset = 10
End Select
For Each cl In Range("DataMyID")
  If xx = Application.Min(Application.CountIf(Range("DataMyID").Offset(, ofset), cl.Offset(, ofset).Value), 6) Then
    'cl.Resize(, 17).Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 17).Value = cl.Resize(, 17).Value
  End If
Next cl
End Sub
There's also a Sheet 1 which can be deleted (it just contains a couple of pivot tables for me to cross check the results with).
 

Attachments

  • ExcelGuru4682Kopie van Referral Program Tracking(jp) (2).xlsm
    94.5 KB · Views: 7
Last edited:
@ p45cal

ask the OP if they have this question double posted or triple posted or cross posted before investing any more of your time in this.
 
This thread may be treated as closed. I have got the solution now. Thanks
 
Thank you p45cal. I have benefited your solution in the end. No one is handling more than six repeated records. Your Macro was excellent. Thanks
 
Hi Pascal, I have a worksheet with your solution. I have found a problem into it as some rows of data are not migrating into its respective columns as the sum of total number of repeated in Column A is also wrong. Please check row No. 299-306, 308,309 etc. The functionality of this worksheet is that when we select a column A1 a list box appear and when we select any particular column and execute the code the repeated rows (in numbers) are migraing into the respective sheet tab. This result is not correctly received in some cases as i have refereed some cell references. Can you please help me again please? here is the worksheet: https://www.dropbox.com/s/616n3yzvz5bre3t/Referral Program Tracking VBA (2).xlsm?dl=0
 
  1. The workbook you link to contains no code nor solution of mine.
  2. Since you have cross posted all over the place and you haven't supplied links to these cross posts, as suggested in the link in msg #5, I can't check whether you have aleady received a suitable answer, so I'd be at risk of duplicating someone else's solution and thus wasting my time.
  3. Hint: 297
 
You must log in or register to reply here.
Back
Top