Find / Vlookup to Return Multiple Values

Marmaduke
Marmaduke used Ask the Experts™
on
I have a monthly spreadsheet that compares customer order data to my data.
The customer uses their own order numbers. I capture these but also have my own reference numbers.
The customer may place more than one order against the same order number, but I would use new reference numbers for each order. Eg, customer order number 12345 would have my order numbers abcde, fghij and klmnop.

Also, sometimes the customer order numbers have info added, like "12345 /new order", or "45678 / 98723". The information is usually separated by a forward slash. The solution needs to look at all the information and not get "confused" by the forward slash and tell me that reference number doesn't exist!!

I need to summarise the data so that I can tell the customer that these are all my order numbers against each of your numbers.
The solution needs to look at all the information in the customer reference col (C) and not get "confused" by the forward slash and tell me that reference number doesn't exist!!

I would like to return all my order numbers, separated by comma's.

Also, I need to know how to then manipulate the solution so I can use it to return other types of data from the same sheet-  eg, customer order date against, order fulfilled date etc.

I can't use a vlookup as I need all the values to be returned.

I don't really know VBA but don't mind using it...I usually have about 1000 lines of data to go through so would really appreciate some help on this!

Example workbook is attached.


Many Thanks,
Marmaduke




Marmaduke-Example.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Marmaduke,

Using the existing data in your workbook, please assemble a report showing exactly what you are wanting as a result and then re-upload that file - thanks
Patrick

Author

Commented:
Patrick,

Sure, but can only do so on Monday!

Thanks!

Author

Commented:
Patrick,

Sure, but can only do so on Monday!

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
Hi Patrick,

I've uploaded the file again with two ways I can have the report.
I would prefer it the way it's shown in "Report Required 2" as this will let me run pivots etc but if this isn't possible then the way it's shown in the Report Required tab is fine.

I found something that kind of does what I need here: http://www.ozgrid.com/forum/showthread.php?t=25239 but this doesn't work properly when closing and reopening the spreadsheet and it seems to make everything run extremely slowly. I usually have to add that module in again every time I reopen and make changes! It may be something I'm doing wrong but it still doesn't provide the information I need "cleanly"!

I hope this makes sense.

Looking forward to your response,
Marmaduke
Marmaduke-Example.xlsx
Marmaduke,

In the attached file you need to do the following to get the data looking as you want:

1. Insert a new column B and in it put =LEFT(D2,8) and copy down to the end of the data.

2. Select all the data and sort it on columns B, E & F

3. Hide column A

4. Conditionally Format column B. Select the whole column, go to Format/Conditional, select 'formula is' and put =B2=B1, press the Format button and select font. With the font, select white as the text colour, press OK and OK again. That will make the duplicate Customer Refs invisible.

5. In column G put =IF(B2<>B1,E2,"") and copy down to the end of the data.

6. In column H put =IF(B2=B3,"",F2) and copy down to the end of the data.

That's it.

Hope it helps.

Patrick


marmaduke-02.xls

Author

Commented:
Hi Patrick,

Thanks for that. Am just working through it now.

M

Author

Commented:
Hi Patrick,

It almost all works -col B has the duplicates whited out - but it shows the last one of that number and it's hard to tell which one you're looking at - I first though the ones below it were duplicates, but then I found there are value above it that are the same and whited out too!
It's probably being finicky but is there any way to change this?

(I may not be able to check for a couple of days as I'm away...)

Uploaded my exaple so you know what I mean!

Thanks
M

Marmaduke-Example.xlsx

Author

Commented:
Also, is there a "quick" way to get the data looking like it does on Report Required - with all my reference numbers on one line against the customer reference numbers?

(I've just been advised by the customer they may prefer this!!)

Thanks
M
Marmaduke,

The code below is in the attached file. Press the button on the 'Report Required' worksheet to produce the report.

Patrick
Sub specialmacro()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long
Dim firstodate As Date
Dim lastfdate As Date
Dim startrow As Long

startrow = 1 'change this to one less than the row on which you want the report to start
Set ws1 = Sheets("Data")
Set ws2 = Sheets("Report Required")
With ws1
    Set rng = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each celle In rng
    On Error Resume Next
    coll.Add CStr(celle), CStr(celle)
Next celle

For i = 1 To coll.Count
    ws2.Cells(i + startrow, 1) = coll(i)
    firstodate = "01/01/3000"
    lastfdate = "01/01/2000"
    For Each celle In rng
        If CStr(celle) = coll(i) Then
            'OurRef
            ws2.Cells(i + startrow, 2) = ws2.Cells(i + startrow, 2) & _
                IIf(ws2.Cells(i + startrow, 2) <> "", ", ", "") & _
                CStr(celle.Offset(0, 1))
            'Order dates
            ws2.Cells(i + startrow, 3) = ws2.Cells(i + startrow, 3) & _
                IIf(ws2.Cells(i + startrow, 3) <> "", ", ", "") & _
                CStr(celle.Offset(0, 3))
            If ws2.Cells(i + startrow, 3) < firstodate Then
                firstodate = ws2.Cells(i + startrow, 3)
            End If
            'Order fulfilled dates
            ws2.Cells(i + startrow, 4) = ws2.Cells(i + startrow, 4) & _
                IIf(ws2.Cells(i + startrow, 4) <> "", ", ", "") & _
                CStr(celle.Offset(0, 4))
            If ws2.Cells(i + startrow, 4) > lastfdate Then
                lastfdate = ws2.Cells(i + startrow, 4)
            End If
        End If
    Next celle
    ws2.Cells(i + startrow, 5) = firstodate
    ws2.Cells(i + startrow, 6) = lastfdate
Next i

End Sub

Open in new window

mduke-example-01.xls
Marmaduke,

I realise you have been an EE member for many years, however have you considered becoming a Premium Service Member and charging it to business expenses. It would allow you to ask as many questions as you like and offer up to 500 points for any or all questions. I believe that as you are doing this work for customers you would get value for money with a Premium Service Membership pretty quickly - particularly when you consider how much you would need to pay if you hired someone to do even just one day's worth of, for example, VBA coding. Anyhow - that was just a thought - hope you don't mind.

Like you I'm just a volunteer here.

Patrick

Author

Commented:
Hi Patrick,

That's actually a very good idea! Thanks for that - and the answers!

I'm actually off work at the moment and won't be back for a couple of weeks so cannot work on the customer spreadsheet until then....
If this question can be put on hold and not "abandoned" that would be great!

Thanks,
Marmaduke

Author

Commented:
Hi Patrick,

I'm back from an extended holiday now so am just looking at this!
Thanks so much for creating that macro.
I only noticed one thing - in the example sheet you uploaded, when the report is produced, row 5 doesn't have accurate information for last fulfilled dates - it's showing as 28/12/09 when it should be 05/01/2010.
The row below is also showing last fulfilled date of 29/12/2009 instead of 30/12/2009.

Thanks,
Marmaduke
Marmaduke,

Let me have a look.

Patrick
Marmaduke,

Please try the attached file/macro.

Patrick

ps at the moment I can't think of a way to eliminate the extra commas.
mduke-example-02.xls

Author

Commented:
Hi Patrick,

Much better thanks! The extra commas are ok, I can live with that but it's also repeated the order fulfilled date after the commas and added an extra date in.
If you look at row 5 in your spreadsheet there are three of our ref's but it's giving 5 Order Fulfilled dates.

If this is something you don't think will work in Excel I'm ok to leave it and try to find some other solution...
You've already helped so much as it is!

(btw, thanks for the advice on premium membership. I checked your profile and we may be neighbours!!! lol)

Marmaduke
>I checked your profile and we may be neighbours!!!

Pinner, Middx

Author

Commented:
Harrow/Wembley, Middx!
Marmaduke,

>If this is something you don't think will work in Excel I'm ok to leave it and try to find some other solution...

Well, the problem is that it is reporting correctly because as you will see in the attached file on the Data sheet there are 5 order fulfillment dates - with one duplicate pair. Now perhaps there shouldn't be 5 order fulfillment dates for CustRef 11331667 but there are in the data you provided. Try deleting the data you don't want in the Data sheet and then re-run the macro.

Patrick
mduke-example-03.xls
>Harrow/Wembley, Middx!

Round the corner - but via the USA.

Author

Commented:
Patrick has been extremely helpful and patient.

Author

Commented:
All working - thnaks so much Patrick!

(and yes, round corner via USA! lol)
Marmaduke - Pleased to hear it worked as designed. Thanks for the grade. Odd that we are no more than 5 miles apart and yet communicate with each other via an 8500 mile link. - Patrick

ps. I can't help asking why you have selected a male screen name when your profile indicates that you are female - or is that just to confuse us all?

Author

Commented:
Yes, the distance is funny - maybe you could come teach me some VBA?!!

Re screen name - my OH set up the profile initially, hence the male name!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial