I am trying to report on if customers buy batteries (linked products) when they buy a product that required batteries.
I am trying to find forumla to populate columns D - F
I am trying to report on if customers buy batteries (linked products) when they buy a product that required batteries.
I am trying to find forumla to populate columns D - F
Re: vlookup to count based on criteria?
Could this be done in a pivot table?
Re: vlookup to count based on criteria?
Try these:
in D3:
[COLOR="#0000FF"]=IF(A3=A2,"",COUNTIF('Linked Products'!$A$2:$A$11,$B3))[/COLOR]
copied down
in E3:
[COLOR="#0000FF"]=IF(A3=A2,"",SUMPRODUCT(--(ISNUMBER(MATCH('Linked Products'!$B$2:$B$11,$B$3:$B$11,0))),--('Linked Products'!$A$2:$A$11=B3)))[/COLOR]
for column F, we need a user defined function to be able to concatenate... .so you will need to add a macro. Hit Alt+F11, then go to Insert> Module then paste this in the editor:
Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
Display More
Then back in F3, enter Array Formula*:
[COLOR="#0000FF"]=SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(MATCH('Linked Products'!$B$2:$B$11,$B$3:$B$11,0))*('Linked Products'!$A$2:$A$11=B3),'Linked Products'!$B$2:$B$11,"")," "))," ",",")[/COLOR]
Copied down
[ARF]x[/ARF]
Note also: You will need to save the workbook as a .xlsm workbook.
Re: vlookup to count based on criteria?
forum.ozgrid.com/index.php?attachment/71670/
Sorry, really struggling to get my head around the formulas,
is it possible someone could amend the attachment? None of the forumlas are working correctly for me.
Re: vlookup to count based on criteria?
Hi,
You were referencing columns A and B of the Related sheet in your formulas when you should have been referencing columns B and C.
Also, your ranges are quite large and will contribute to slow processing of your formulas...
I applied a dynamic named range called "Related" to your related sheet table. This will allow the table to grow or shrink and still be referenced in your formulas.
I also shrunk the references in your main sheet to 6500 rows.... you should keep this to minimum needed...
Please see attached. Warning... it will take a while to recalculate.
If you need it to process faster, then you'll need someone to provide you a vba solution or rethink the process.
Re: vlookup to count based on criteria?
It doesn't calculate correctly.
For order [TABLE="width: 87"]
[TD="class: xl63, width: 87"]O0674578[/TD]
[/TABLE]
column P should be 0 and Q should be blank because no linked skus were purchased.
Also there are no calculations in column O
Also for sku 31193, cell N4 should be 0.
For order O0665619 P5 should be 1 and Q5 should be 10027
Thanks
Re: vlookup to count based on criteria?
Can anyone think of a good way of analysing if linked products are sold in orders?
Don’t have an account yet? Register yourself now and be a part of our community!