Change Data layout in excel

razor19 0
Viewed 30

Hi guys I have excel data taken from vendor in undesired format. I want to change the format of layout so that it would be easier for me to ingest this data to database such as (Postgre / Mysql)

The image below would be best way to describe the situation:

enter image description here

There are thousands of file in this format as one file contain informations of only sales in one date. Is there a better or fast way to convert this data? or creating vba is the only way? Anyone ever deal with this sample of data can kindly give a hint of vba code?

Thank you

vba excel
1 Answers

We could definitely do this in VBA! On the other hand, why bother when it can easily be accomplished with excel formulas?

My assumptions:

  • You're dealing with data of variable sizes (number of regions and items)
  • Cells you show as merged are merged on the actual data.
  • I set this up to work with a max data range of row 100, but easy to change that.

Step 1) Identify how many regions and items we have:

  • =COUNTA($D$4:$K$4) (Regions) CountA

  • =COUNTA($C$6:$C$100) (Items) countaitems

Step 2) List Items, repeating for number of regions.

  • Use local line numbers rather than worksheet row numbers to make this more portable.
    To make it repeat, we're going to divide line# by total regions, roundup, then index items using that. Don't forget error handling to identify when to stop listing items. From now on, all the rest of our formulas will have a If( *item column* <> "", *show stuff* , *don't show stuff* ) . We can gloss over that in future steps.

  • =IFERROR(INDEX($C$6:$C$100,MATCH(ROUNDUP(M5/$P$1,0),$B$6:$B$100,0)),"") itemlisting

Step 3) Date is super easy:

  • Just pull the same date for each: =IF($O5<>"",$C$2,"")

Step 4) We're going to use the Mod() function to create a cyclic action with our regions.

  • It took me a while to play with the formula... so don't ask me what all the adjustments(translation and scaling) are for, just trust it works. =IF($O5<>"",INDEX($D$4:$K$4,2*(MOD($M5-1,$P$1)+1)-1),"") Regions

Step 5) "Quantity" and "Price" are the same formula, except "Price" has an extra "+1"

  • Quantity = =IFERROR(INDEX($D$6:$K$100,MATCH($O5,$C$6:$C$100,0),MATCH($P5,$D$4:$K$4,0)),"")
  • Price = =IFERROR(INDEX($D$6:$K$100,MATCH($O5,$C$6:$C$100,0),MATCH($P5,$D$4:$K$4,0)+1),"") Quantity

FINAL PRODUCT:

final

Cameron Critchlow0
Related