Searching for values and creating a list [Solved/Closed]
Posts Friday November 18, 2016Registration date
November 18, 2016
Nov 18, 2016 at 08:32 AM - Latest reply: yg_be5225
Posts Sunday June 8, 2008Registration dateContributorStatus
April 20, 2018
- Nov 18, 2016 at 03:08 PM
I hope you can help me, so far I have managed to create a formula which searches a range of data (SKU’s) and returns a count of the occurrences of that SKU. The search includes where the SKU forms part of a larger group of SKU’s. E.g. SKU ‘PL009’ is listed as ‘PL009’, ‘PL009 SB420’ and ‘SB010 PL009 SK008’ and therefore my formula returns the value 3 which is what I want. The formula i have used is this: =COUNTIF(Q$2:Q$786,"*"&T2&"*")
I would now like to go one step further and for the SKU’s where the count is more than one I would like the values to be returned in a list linked to the SKU searched for. Should i be using a pivot table? I'm a bit green with excel and please i'd managed to get as far as i have but i'm now stumped!
I assume your SKUs are in column Q. What do you have in T2, and, perhaps, in whole column T? Where is the formula stored?
It is hard to assist you because you do not actually describe what you globally need to achieve.
Function skus(inp As Range, sing_sku As String) As String
Dim cell As Range
skus = ""
For Each cell In inp
If InStr(cell, sing_sku) > 0 Then
skus = skus + "|" + CStr(cell.Value)