Data formula to transpose data from rows to columns

Since the Product Description (Egg) and Cat (Breakfast) relate directly to the PLU Number, you can get them with simple Index-Match formulae:

Formula:

Please Login or Register  to view this content.

The Site Prices are a bit trickier but you can do it by concatenating what you want to lookup - combining the PLU No with the Site, like this in K5 (note the two & symbols to join the search terms and the columns being checked):

Formula:

Please Login or Register  to view this content.

Important: this is an array formula so needs to be entered using Ctrl-Shift-Enter not just Enter. You will know the array is active when you see curly brackets { } appear around your formula - don't try to enter them yourself. If you do not Ctrl-Shift-Enter an array formula you will get an error or a clearly incorrect result. If that happens, just click into the formula bar and try again.

You can then drag this formula to the right and you'll get the prices for the other columns.
Then you can select J5:P5 and drag all the formulae down together to get the other rows (note - the answers don't match the figures in your right-hand table, but are correct from the left-hand one).

Hope that does what you need.

How about a Pivot Table as shown in the attached.

Data Range

	I	J	K	L	M	N	O
25	Sum of Site Price		Site
26	Product description	Cat	Midlands	North	Scotland	South	Wales
27	Apple Pie	Deserts	0.8	0.8	0.7	0.8	0.6
28	Bacon	Breakfast	1.1	1.1	0.95	1.2	1.4
29	Coffee	Drinks	1	0.9	0.7	1.1	1.2
30	Egg	Breakfast	0.3	0.4	0.5	0.2	0.4
31	Scampi	Lunch	0.42	0.41	0.45	0.43	0.44

Buonasera a tutti
in I5 da trascinare a destra fino a J5 e poi in basso

SE.ERRORE(INDICE(A$5:A$100;CONFRONTA(0;INDICE(CONTA.SE(I$4:I4;A$5:A$100&"");0));"")


in K5 da trascinare a destra fino a P5 poi in basso

=SE.ERRORE(INDICE(SCEGLI(SE(COLONNE($A$1:A1)<=5;1;2);$D$5:$D$100;$E$5:$E$100);AGGREGA(15;6;RIF.RIGA($A$5:$A$100)/($B$5:$B$100=$J5)-RIF.RIGA($A$5)+1;RESTO(RIF.COLONNA(A1)-1;5)+1));"")

For the errors in the right-hand table, you can wrap the array formula in IfError:

Formula:

Please Login or Register  to view this content.

As it's already an array formula, adding the error catch shouldn't have much of an effect on speed.

To highlight blanks in the left-hand table, I suggest using Conditional Formatting.
1. Select your data in column A (A4:A29 in your sample, amend as needed for your real data - add enough rows to allow the data to grow - it won't make much speed difference if you add a few extra thousand).
2. With those cells still selected, on the Home tab, click Conditional Formatting then New Rule then Use a formula to determine which cells to format.
3. Enter this formula in the box at the bottom:
=LEN(A4)=0
4. Click the Format button and choose how you want to highlight the cells - red fill is generally good.
5. Click OK.

Hope that does what you want.

You're welcome, glad we could help and thanks for the rep.