Previous Topic Next Topic

Searching for values and creating a list

Solved/Closed
kswing Posts 4 Registration date Friday November 18, 2016 Status Member Last seen November 18, 2016 - Nov 18, 2016 at 08:32 AM
yg_be Posts 23405 Registration date Sunday June 8, 2008 Status Contributor Last seen December 20, 2024 - Nov 18, 2016 at 03:08 PM
Hi there,
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!

Any help would be appreciated.
Thanks
Kathryn

2 responses

Answer 1 / 2
yg_be Posts 23405 Registration date Sunday June 8, 2008 Status Contributor Last seen December 20, 2024 5
Nov 18, 2016 at 08:50 AM
Kathryn,
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.
0
kswing Posts 4 Registration date Friday November 18, 2016 Status Member Last seen November 18, 2016
Nov 18, 2016 at 09:06 AM
Hi,

Thank you for replying. To clarify

Column T contains the singular list of SKU's i.e. 'PLU009'. There are 664 of these in total. Column Q is a complete list of our SKU's including those in column T (786 in total), it includes the single instance i.e PLU009 and any combination of it. e.g 'PLU009 SB009'.

Having done a simple count of the instances the SKU's (Column U) I now want to have the detail behind that count. E.g where column U shows 3, i want the data behind the count so that i can see in real terms 3 is actually PLU009, ‘PL009 SB420’ and ‘SB010 PL009 SK008’. I hope that makes sense
0
Answer 2 / 2
yg_be Posts 23405 Registration date Sunday June 8, 2008 Status Contributor Last seen December 20, 2024 5
Nov 18, 2016 at 09:29 AM
Suggestion :
1) create the following function : 
Option Explicit
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)
        End If
    Next
End Function

2) use following formula : 
=SKUS(Q$2:Q$786,T2)
0
kswing Posts 4 Registration date Friday November 18, 2016 Status Member Last seen November 18, 2016
Nov 18, 2016 at 09:42 AM
Thanks, this looks interesting.. I'm really sorry but where and how do i create the function?
0
yg_be Posts 23405 Registration date Sunday June 8, 2008 Status Contributor Last seen December 20, 2024 5
Nov 18, 2016 at 10:05 AM
Does this help? https://support.microsoft.com/en-us/office/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f?ui=en-us&rs=en-us&ad=us
Don't be sorry, Kathryn.
0
kswing Posts 4 Registration date Friday November 18, 2016 Status Member Last seen November 18, 2016
Nov 18, 2016 at 02:49 PM
You are brilliant....I read the support notes and created the function you suggested, I now have the data I want. Thank you so so much!
0
yg_be Posts 23405 Registration date Sunday June 8, 2008 Status Contributor Last seen December 20, 2024 5
Nov 18, 2016 at 03:08 PM
thank you for your positive feedback, it was a pleasure to help you.
0

Similar discussions

Facebook Messenger - Suggested Friends
jadess_0462 -
Akajackal -

2 responses
Why can't I bridge connect?
ryno -
Qiqi -

15 responses
Anyone's dm order all weird ?
Tillyleon111 -
toogleeful -

2 responses
What is the first name of your favorite uncle?
Yusieda88 -
Ambucias -

1 response
help me to add particular cells
mathewmunna -
mathew munna -

2 responses