Possible INDIRECT function

[andybrst]

Hi

I have a table in Sheet 1 listing order items. I would like to convert the list to a single row for each order number.

Sheet1
Order number Product Detail Qty
1 Poultry ****erel 10lb 1
1 Pies Pork Pie 4lb 1
1 Pies Pork Pie 1lb 0.5
2 Beef Rolled Sirloin 6lb 1
2 Pork Belly 7lb 6
2 Others Mince (oz) 4
2 Sausages Pigs in Blanket (lbs) 3
2 Bacon Smoked Streaky (lbs) 1
2 Gammon Gammon 4lb 1
2 Others Sausage Roll 12


Sheet2
Order number Item1 Item 2 Item 3 Item 4
1 ****erel 10lb Pork Pie 4lb Pork Pie 1lb
2 Rolled Sirloin 6lb Belly 7lb Mince (oz) Pigs in Blanket (lbs)



I have been trying to use the INDIRECT function, but it isn't working.

Can anyone help?

thank you in advance

 

[Nutster]

Can you please post the formula you tried to use that failed to give us a starting point to point you in the correct direction?

 

[andybrst]

This is what I used

=INDIRECT("'" & $A$1 & "'!" & "c2")

Cell A1 on Sheet2 was the name of the detail sheet.

 

[Nutster]

This is what I used

=INDIRECT("'" & $A$1 & "'!" & "c2")

Cell A1 on Sheet2 was the name of the detail sheet.

You don't need to concatenate two text literals; you can just combine them.

=INDIRECT("'"& $A$1 & "!C2")

To extract data from another cell, you want to use something like the VLOOKUP or INDEX functions. The issue I see here is adjusting the lookup range each time to skip past the items already shown in your row. If the order items will be unique for each order (you won't have the same item show up twice in the same order), then you can use INDIRECT to build your VLOOKUP lookup range each time.

I tried reproducing your output and had to use more than just an INDIRECT to do it.
[TABLE="class: grid, width: 1100"]
<tbody>[TR]
[TD]Summary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order number[/TD]
[TD]Item Count[/TD]
[TD]Item 1[/TD]
[TD]Item 2[/TD]
[TD]Item 3[/TD]
[TD]Item 4[/TD]
[TD]Item 5[/TD]
[TD]Item 6[/TD]
[TD]Item 7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]****erel 10lb[/TD]
[TD]Pork Pie 4lb[/TD]
[TD]Pork Pie 1lb[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD]Rolled Sirloin 6lb[/TD]
[TD]Belly 7lb[/TD]
[TD]Mince (oz)[/TD]
[TD]Pigs in Blanket (lbs)[/TD]
[TD]Smoked Streaky (lbs)[/TD]
[TD]Gammon 4lb[/TD]
[TD]Sausage Roll[/TD]
[/TR]
</tbody>[/TABLE]

I put your original data (Sheet1) in the range A1:D11 and the above summary in F1:N4 on the same sheet. The #N/A cells indicate that there was no more items for that order.

The Item Count I got using COUNTIF.

=COUNTIF($A$2:$A$11,$F3)

So, this is looking for the Order Number in the original data and counting how many have the same value as the order number in the summary. Order number 1 is in F3. I just copied the formula down for order 2.

Item 1 was retried using VLOOKUP.

=VLOOKUP($F3,$A:$D,3,FALSE)

Look for the value in F3 (1 for order number) in A:D (the transaction detail), returning the third column (item name). The FALSE at the end tells VLOOKUP to find an exact match using a linear search.

Item 2 and so on was a little trickier as I had to use an array version of MATCH to find the previous items and start after that.

{=VLOOKUP($F3,INDIRECT("A"&MATCH($F3&H3,$A$1:$A$12 & $C$1:$C$12,0)+1&":D21",TRUE),3,FALSE)}

The VLOOKUP is the same as the previous VLOOKUP, except the search range is coming from the INDIRECT function, with a nested MATCH function to skip over the last item. The MATCH function looks in the transaction data to find the last item for this order. By combining the order number ($F3) and the name of the previous item (H3), MATCH can look in both columns (A & C) at once to avoid issues if I look just for one. I then add 1 to that row to start looking for the next item in the next row. This was fed into INDIRECT to build the range that VLOOKUP would consider for the next item of the order. In order for the array concatenation ($A$1:$A$12 & $C$1:$C$12) to work, you have to press Ctrl+Shift+Enter when done entering the function, not just Enter or moving to another cell; otherwise, you will get a #VALUE error.

 

[andybrst]

Thank you so much for this.
It worked perfectly.