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?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Mis-aligned data
- Thread starter Hatter
- Start date
kvsrinivasamurthy
Well-known Member
- Joined
- Nov 6, 2013
- Messages
- 1,273
It is better post the file with few rows in some website and give link here.
Upvote
0
| 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 |
Example above, aim is for a report/graph to be shown in percentages on GR and Invoice variances. The data comes out mis-aligned, Supplier C has two orders and supplier data is not directly above as like the others.
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,286
- Office Version
- 365
- Platform
- Windows
I think we are still very confused as to your ultimate design goal here.
To help people help you, make it easy to understand for us.
One simple way is to show us a complete example (you know what they say, "a picture says 1000 words!).
By complete example, I mean the following:
1. Show us the BEFORE picture (what your data looks like to start)
2. Show us the AFTER picture (what you want it to look like after the VBA code runs to do the changes you want)
You have shown us one of those pictures (I assume the BEFORE), but not the second.
Please show us the second, for extra sample data you provided.
To help people help you, make it easy to understand for us.
One simple way is to show us a complete example (you know what they say, "a picture says 1000 words!).
By complete example, I mean the following:
1. Show us the BEFORE picture (what your data looks like to start)
2. Show us the AFTER picture (what you want it to look like after the VBA code runs to do the changes you want)
You have shown us one of those pictures (I assume the BEFORE), but not the second.
Please show us the second, for extra sample data you provided.
Upvote
0
Alex Blakenburg
MrExcel MVP
- Joined
- Feb 23, 2021
- Messages
- 8,139
- Office Version
- 365
- Platform
- Windows
How about we start with this and you tell us where you want to go from here ?
We can use VBA to produce this if or a variation if you give us more detail on what you want to finish up with.
In the below just delete the left hand side, copy in the latest data at A1, make sure the formulas extend to the bottom on the right hand side.
Then put a filter or pivot over the right hand side.
We can use VBA to produce this if or a variation if you give us more detail on what you want to finish up with.
In the below just delete the left hand side, copy in the latest data at A1, make sure the formulas extend to the bottom on the right hand side.
Then put a filter or pivot over the right hand side.
| 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,""))) |
Upvote
0
Similar threads
- Question
- Solved
