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.
Find / Vlookup to Return Multiple Values
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
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
Do more with
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
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
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
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
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
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
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
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
(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
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
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
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
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
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
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
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,
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
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
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
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
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
>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
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?
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?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial