Identify value based on criteria

[kaushik03]

Hi All,

I am trying to get a code for the following query:

Let’s first discuss about the sheets I have:

Parent data: Col B contains the name of the clients (X1, X2 X3…..so on) and Col A contains unique project IDs submitted by these clients

Pivot: Here I run a pivot to see the number of projects submitted by each one of these clients

Targeting clients: Here I have identified some clients (say according to last month’s data) who have submitted >= 4 projects. They are X1, X2, X3, X5 and X6 (in the pivot you could see X7 also has the count of 4 but I have not included that in my initial target list….I will explain this later)

Note: Every month new data is added in parent sheet from last available row onwards and my pivot table is accordingly refreshed with new data points.

Requirement:

IF any one of the client’s in pivot table (Col A of ‘Pivot’ sheet) exists in my targeted clients’ list (Col A of ‘Targeting clients’ sheet)THEN

‘do nothing’ and come to the next row ( of Col A of ‘Pivot’ sheet)

Else: (does not exist in my targeted clients’ list)

Check the corresponding cell (Col B of pivot table)…..IF the value is found to be >=4 THEN copy that corresponding client’s name (In Col A of Pivot table) and paste it in the last available row at Col A of ‘Targeting clients’ sheet…….ELSE(IF the value is found to be <4) ‘do nothing.

Most precisely the condition is:

1) IF clients’ match happens…THEN don’t do anything….come to the next row(at pivot table)

2) IF clients do not match….THEN check the corresponding value

a. IF the value>=4 THEN perform copy paste action

b. IF the value <4 THEN don’t do anything….come to the next row(at pivot table)

Say, on this month one client submits >=4 projects and that client does not exist in my targeting clients’ list (Hence, here X7 is that example).

So, taking the above scenario into consideration, code should identify X7 and paste it in the last available row at Col A of ‘Targeting clients’ sheet


Here is the file

https://hotfile.com/dl/167285764/28b04d0/Sample_Targetted_clients.xlsx.html


Kaushik

 

[Debraj]

Hi Kaushik,


* I dont have enough knowledge in PIVOT TABLE. So you know "Alpa bidya bhyankari".. No comment in PIVOT.


* But I have manage to done the same using VBA.

[pre]

Sub CheckData()
LRinPD = Sheets("Parent data").Range("A65536").End(xlUp).Row
With Application.WorksheetFunction
For i = 2 To LRinPD
LRinTC = Sheets("Targetting clients").Range("A65536").End(xlUp).Row
If .CountIf(Sheets("Parent data").Range("B:B"), Sheets("Parent data").Range("B" & i)) >= 4 And _
.CountIf(Sheets("Targetting clients").Range("A1:A" & LRinTC), Sheets("Parent data").Range("B" & i)) = 0 Then
Sheets("Targetting clients").Range("a" & LRinTC + 1) = Sheets("Parent data").Range("B" & i)
End If
Next i
End With
End Sub

[/pre]
Please let me know it it works for you..


Regards,

Deb


PS: I would also like to know the solution in non-Scripting..

PPS : community clarification... aka "A little knowledge is a dangerous thing!"

 

[kaushik03]

Hi Debraj,


It was just mervellous....works like a gem...


Your few lines of code give me the ultimate relief....Thank you very much dear....you are just awesome...


Note: I did not recieve you email ID in the attached file you shared last time..can you plz send me a test e-mail here pharmacyjukaushik@yahoo.com


[jekono VBA problem er jonno, prochur jalaton korbo ebar tomai..]


Kaushik

 

[Debraj]

Thanks Kaushik, and Happy to help anytime...


Last night a Excel SPY already shared a thriller Novel,in addition to my mail-ID, with you..


Regards,

Deb

 

[SirJB7]

@kaushik03

Hi!

I think it's a good practice to translate into English words or phrases written in other languages, so as all the community could understand the whole comment.

Regards!