ExcelBanter - Lookup formula for purchased items with same invoice number

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Lookup formula for purchased items with same invoice number (https://www.excelbanter.com/excel-discussion-misc-queries/141946-lookup-formula-purchased-items-same-invoice-number.html)

Lookup formula for purchased items with same invoice number

Hi,

I've got a list of invoice numbers against items purchased. Some invoices
have more than one item (multiple items) purchased as follows:

A B
1 Invoice Number Item Purchased
2 181 ACCESSORY
3 181 COMPUTER
4 181 CAMERA
5 180 ACCESSORY
6 179 CAMERA
7 179 CONSOLE
8 177 COMPUTER
9 176 COMPUTER
10 176 COMPUTER
11 175 CAMERA

I need a lookup type formula in cell B2 to B7 where I get the results as
below:

A B
1 Invoice Number Property Description
2 181 ACCESSORY, COMPUTER, CAMERA
3 180 ACCESSORY
4 179 CAMERA, CONSOLE
5 177 COMPUTER
6 176 COMPUTER, COMPUTER
7 175 CAMERA

Does anybody have any ideas? Any help would be gratefully received.

Thank-you,

John

Lookup formula for purchased items with same invoice number

Many thanks for your reply.

I get a run time error on:
MsgBox "Report" & i & " " & Right(ms, Len(ms) - 1)

What was the other recent posting? Prehasps I can find the answer here. I
have spent the last 5 hours on officekb looking for answers, but could not
get any to work.

Regards,

J

Don Guillett wrote:
From a recent posting of mine on a very similar question. Homework?

Sub makelist()
For i = 1 To 33
ms = ""
For Each c In Range("ai2:i5")
If Left(c, 7) = "Report" & i Then ms = ms & "," & c.Offset(, 1)
Next c
MsgBox "Report" & i & " " & Right(ms, Len(ms) - 1)
Cells(i, "k").Value = "Report" & i & " " & Right(ms, Len(ms) - 1)
Next i
End Sub

Hi,

[quoted text clipped - 31 lines]

John

Lookup formula for purchased items with same invoice number

Assumes your invoice #'s in col g and items in col H AND a list of desired
in col I
Invoice Item
181 ACCESSORY 181 181 ACCESSORY,COMPUTER,CAMERA
181 COMPUTER 179 179 CAMERA,CONSOLE
181 CAMERA 177 177 COMPUTER
180 ACCESSORY 176 176 COMPUTER,COMPUTER
179 CAMERA 175 175 CAMERA
179 CONSOLE

177 COMPUTER
176 COMPUTER
176 COMPUTER
175 CAMERA

Sub makelistinvoices() 'Don Guillett
For Each c In Range("i2:i6")
ms = ""
For i = 1 To Cells(Rows.Count, "g").End(xlUp).Row
If c.Value = Cells(i, "g") Then ms = ms & "," & Cells(i, "h")
Next i
MsgBox c & " " & Right(ms, Len(ms) - 1)
c.Offset(, 1) = c & " " & Right(ms, Len(ms) - 1)
Next c
End Sub

--
Don Guillett
SalesAid Software

"Johnds" <u34025@uwe wrote in message news:71dfba46a663c@uwe...
Many thanks for your reply.

I get a run time error on:
MsgBox "Report" & i & " " & Right(ms, Len(ms) - 1)

What was the other recent posting? Prehasps I can find the answer here.
I
have spent the last 5 hours on officekb looking for answers, but could not
get any to work.

Regards,

J

Don Guillett wrote:
From a recent posting of mine on a very similar question. Homework?

Sub makelist()
For i = 1 To 33
ms = ""
For Each c In Range("ai2:i5")
If Left(c, 7) = "Report" & i Then ms = ms & "," & c.Offset(, 1)
Next c
MsgBox "Report" & i & " " & Right(ms, Len(ms) - 1)
Cells(i, "k").Value = "Report" & i & " " & Right(ms, Len(ms) - 1)
Next i
End Sub

Hi,

[quoted text clipped - 31 lines]

John

Lookup formula for purchased items with same invoice number

I've also manged to use the following forumlas from a previous post/thread to
get my desired results:

With the receipts starting in E1, and the order numbers starting in F1.

Your list of unique, sequential receipt numbers to look up starts in M1.

Enter this array formula in N1, but use a regular <Enter:

=INDEX($F$1:$F$1000,SMALL(IF($E$1:$E$1000=M1,ROW($ E$1:$E$1000),""),TRANSPOSE
(ROW($E$1:$E$1000))))

Now, if you read the old post that I linked to, you'll see that to insure
that *all* the order numbers are returned, you should have more formulas
going across the columns then you have order numbers, so that you receive at
least one error, telling you that *all* orders have been returned, and there
are none left, so that errors (#NUM!) are generated.
Otherwise, you'll never be *sure* that you have them all.

If you guess that you might have 10 orders, you should copy this formula
across 10 columns.
BUT ... DON'T REALLY COPY!

Select N1, but *DON'T* use the "fill handle".
Simply click and drag the *selection* across 10 columns.
This gives you the formula in N1, which is colored white, and the rest of
the selection, which is colored grey.
NOW, do a <F2, and then <Ctrl <Shift <Enter.

You now have your array formula copied across 10 columns, and with an
existing receipt number in M1, you should have all pertinant order numbers
displayed in the row, and hopefully, at least one #NUM! error, telling you
that you have *all* the existing order numbers returned.

You can now select the row of 10 array formulas, and copy them down in the
*regular* way, using the "fill handle".

finally used a forumula similar to =A1&","&B1&","&C1 to seperate values by a
comma.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200705/1

Lookup formula for purchased items with same invoice number