Hi, need assistance on where to begin. I have data that once in excel is mis-aligned, supplier names above PO etc. A) is there a way to get the data re-aligned or B) a formula that would look at the next cell upwards that has text?
Order | Received | Variance | Supplier | Invoice | Var | |||||||||
Supplier / Order No - Required - Status / Product | UoM | Qty | Kgs | UoM | Qty | Kgs | Qty | Kgs | Unit £ | Total £ | Total £ | £ | ||
000001 - Supplier A | 110 | 110.00 | 110 | 110.00 | 100% | 100% | 389.4 | 355.3 | 91% | |||||
PO1234 - 09/01/2024 - Complete | 110 | 110.00 | 110 | 110.00 | 100% | 100% | 389.4 | 355.3 | 91% | |||||
10000000 - Product Z | EACH | 110 | 110.00 | EACH | 110 | 110.00 | 100% | 100% | 3.54 | 389.4 | ||||
000002 - Supplier B | 28000 | 28000.00 | 28100 | 28100.00 | 100% | 100% | 39200 | 39340 | 100% | |||||
PO1235 - 09/01/2024 - Complete | 28000 | 28000.00 | 28100 | 28100.00 | 100% | 100% | 39200 | 39340 | 100% | |||||
100000001- Product Y | EACH | 28000 | 28000.00 | EACH | 28100 | 28100.00 | 100% | 100% | 1.4 | 39200 | ||||
000003- Supplier C | 660 | 32.74 | 651 | 32.29 | 99% | 99% | 37665 | 48555 | 129% | |||||
PO1236 - 09/01/2024 - Complete | 390 | 19.35 | 390 | 19.35 | 100% | 100% | 21060 | 21060 | 100% | |||||
10000002 - Product X | CASE | 390 | 19.35 | CASE | 390 | 19.35 | 100% | 100% | 54 | 21060 | ||||
PO1237 - 09/01/2024 - Live | 270 | 13.39 | 261 | 12.95 | 97% | 97% | 16605 | 27495 | 166% | |||||
10000003 - Product W | CASE | 270 | 13.39 | CASE | 261 | 12.95 | 97% | 97% | 61.5 | 16605 | ||||
000004 - Supplier D | 1920 | 1920.00 | 1920 | 1920.00 | 100% | 100% | 7555.2 | 7555.2 | 100% | |||||
PO1238 - 09/01/2024 - Complete | 1920 | 1920.00 | 1920 | 1920.00 | 100% | 100% | 7555.2 | 7555.2 | 100% | |||||
10000004 - Product V | EACH | 1920 | 1920.00 | EACH | 1920 | 1920.00 | 100% | 100% | 3.935 | 7555.2 | ||||
000005 - Supplier E | 7080 | 7080.00 | 7080 | 7080.00 | 100% | 100% | 42293.28 | 42293.28 | 100% | |||||
PO1239 - 09/01/2024 - Complete | 7080 | 7080.00 | 7080 | 7080.00 | 100% | 100% | 42293.28 | 42293.28 | 100% | |||||
10000005 - Product U | EACH | 1080 | 1080.00 | EACH | 1080 | 1080.00 | 100% | 100% | 6.116 | 6605.28 | ||||
10000006 - Product T | EACH | 6000 | 6000.00 | EACH | 6000 | 6000.00 | 100% | 100% | 5.948 | 35688 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
Q2:Q19 | Q2 | =IF($A2<>"","Supplier",IF($B2<>"","PO",IF($C2<>"","Product",""))) |
R2:T19 | R2 | =IF(A2<>"",A2,IF(ROW(A1)<>1,R1,"")) |
U2:AF19 | U2 | =IF($A2<>"",D2,IF($B2<>"",C2,IF($C2<>"",D2,""))) |