• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Tally Data with multiple criteria

A

Abhijeet

Active Member
Hi

I have Process Data & Allocation Data i want to tally Both with Trust,batch Name & how many Counts of Process etc

I show Tally sheet how to Counts want to pull i used Formula Only Problem how to lookup Allocation Data with Process data please tell me how to do this
 

Attachments

  • Tally Data Ver1.1.xlsx
    15 KB · Views: 11
No need to add that criteria if add no issue if not add that is also fine

why u feel that should be add in criteria
 
I know it is easy but problem is how to pull allocation sheet data pull bcoz

in batch name in allocation is Batch S003 & Process data sheet mention like this
02.02.2016 100 Batch S003.TIF so how to lookup this kind of

Please tell me if any macro solution
 
I use this formula but i want to add in criteria Trust so please tell how to match trust also
=INDEX('Allocation Data'!C$2:C$4,MATCH("*"&'Allocation Data'!$B2&"*",Tally!$B$6:$B$8,0))
 

Attachments

  • Tally Data Ver1.1.xlsx
    15.4 KB · Views: 4
Hi Abhijeet,

In Sheet3, empty column put following formula (say cell X3) and copy down.
=LOOKUP(99,SEARCH('Allocation Data'!$B$2:$B$4,Tally!B3),'Allocation Data'!$B$2:$B$4)

It will pull the partial match result. I am banking on assumption that there will be one batch name on both sides.
 
Hi Abhijeet,

You can then CONCATENATE those two fields to get your unique LOOKUP KEY combination. e.g.

If you modify above formula like below then it will give you a combination key like "100Batch S003" which you can look it up using Index formula or you can use COUNTIFS the way you have used.
=A3&LOOKUP(99,SEARCH('Allocation Data'!$B$2:$B$4,Tally!B3),'Allocation Data'!$B$2:$B$4)
 
Hi Abhijeet,

What is not working? The formula posted above is for fetching data as one of the fields (batch name) is not matching. Above formula can do the partial match and return the results.

Once you have two fields to match then you can employ COUNTIFS or any other formula that you intend to apply.

Does this clear your situation?
 
This formula =A3&LOOKUP(99,SEARCH('Allocation Data'!$B$2:$B$4,Tally!B3),'Allocation Data'!$B$2:$B$4) joint only Tally sheet A3&B3 but i want to match same in Allocation sheet as well

If A3 is 100 mention B3 is Batch S003 mention If Allocation Sheet Batch S003 for 103 then this case ur formula give wrong result
 
Hi Abhijeet,

It is sometimes hard to figure out what you require and communication sometimes confuses me further. And please take no offence, you also need to improve your written communication.

Maybe, a manual example showing what do you want to achieve exactly would be really good.
 
I want exact match trust & batch name both sheets i.e (Sheet 2 & 3) if match then pull data from Sheet 2 into sheet 3 like this
upload_2016-2-28_15-41-14.png
 
I am not 100% sure. In Cell V3 put following formula and copy down:
=INDEX('Allocation Data'!$I$2:$I$4,LOOKUP(2,1/ISNUMBER(FIND('Allocation Data'!$B$2:$B$4,Tally!B3)*FIND('Allocation Data'!$A$2:$A$4,Tally!A3)),ROW('Allocation Data'!$I$2:$I$4)-1))

Check if it is what you are after.
 
Its work

Can u pls tell me Macro is this possible to pull all match data & non Match data in different sheets
 
Try below code. Make sure you read comments before you use it.
Code: 
Sub ProcessData()
Dim wksSource As Worksheet, wksDestn As Worksheet
Dim lngSrcLastRow As Long, lngDstLastRow As Long, i As Long, j As Long

'\\ Set necessary references
Set wksSource = Worksheets("Allocation Data")
Set wksDestn = Worksheets("Tally")
lngSrcLastRow = wksSource.Range("A" & Rows.Count).End(xlUp).Row
lngDstLastRow = wksDestn.Range("A" & Rows.Count).End(xlUp).Row

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'\\Loop through ranges on both sheets
For i = 2 To lngSrcLastRow
    For j = 2 To lngDstLastRow
        If wksDestn.Range("A" & j).Value = wksSource.Range("A" & i).Value And _
        InStr(wksDestn.Range("B" & j).Value, wksSource.Range("B" & i).Value) > 0 Then
            wksDestn.Range("V" & j).Value = wksSource.Range("I" & i).Value
            '\\ Remove comment mark from below line if you expect only ONE match
            'Exit for
        End If
    Next j
Next i

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
 
You must log in or register to reply here.
Back
Top