Transferring raw data into my own sheet

Ramballah

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
332
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

Not even sure if the title of this thread is correctly formulated so I apologize in advance. Anyway, In our systems we have some data that needs proper analyzing and for some reason our system can't do that and the boss don't want to invest into other systems so I took it upon my self to try it in excel. I can copy the raw data into excel but I am running into a few issues:

1. The data I post needs to be grabbed from sheet1 into sheet2 but then properly organized
2. The column "date" is posted as in 01.08.2024 and for some reason excel don't know how to sort that from a-z so maybe the formula to grab the data can also do that idk?
3. There are double data inputs that are slightly different but I need those aswell.
4. The sheet is somewhat dynamic. Everyweek I need to add new data to it so the other sheet needs to be capable of adding those rows aswell
5. The amount values are in the minus and some in the plus. I need that to be reversed actually but thats for a later issue.

Anyway this sounds all confusing without showing what im working with:

derving zuivel.xlsx
ABCDEFGHI
1DateItem IDItem NameAmountPieceTypeItem CostnvtTotal Cost
201.08.2024602972JUMBO GEKLEURDE EIEREN 6-9BAKOvercode-22,410-22,41
301.08.2024602972JUMBO GEKLEURDE EIEREN 6-10BAKOvercode-24,90-24,9
401.08.2024602648JUMBO SCHARREL EI 6ST M/L-1DSBreuk-2,990-2,99
501.08.2024352587JUMBO DRINKYOGH 0% BANAAN-5PAKOvercode-6,450-6,45
601.08.2024432527OPTIME DRINK BANAAN 1L-1PAKOvercode-1,990-1,99
701.08.2024526943CAMPIN FRUITMELK BANAAN-1PAKOvercode-1,750-1,75
801.08.2024547425ACTIME KIDS DRINK AARDBEI-1PAKOvercode-2,340-2,34
901.08.2024559932CAMPIN VOLLE MELK 1,5L-3PAKOvercode-6,450-6,45
1001.08.2024559932CAMPIN VOLLE MELK 1,5L-3PAKOvercode-6,450-6,45
1101.08.2024102408MELKUN HAVERMOUTPAP-1PAKOvercode-2,190-2,19
1201.08.2024183639CAMPIN KWARK VOL 500GR-5STKOvercode-10,20-10,2
1301.08.2024206420CAMPIN VOLLE YOGHURT 1,5L-1STKOvercode-2,490-2,49
1401.08.2024221284JUMBO BIOGARDE HV STANDYO-5BKROvercode-6,950-6,95
1501.08.2024304545JUMBO BOLLETJES VANILLEVL-11PAKOvercode-19,690-19,69
1601.08.2024308005MONA PUD FRAMBOOS MS-5BLKOvercode-12,350-12,35
1701.08.2024369633OETKER PAULA VAN/CHOC 4PK-1PAKOvercode-2,590-2,59
1801.08.2024500806JUMBO BLK MAGER YOGH 0,5L-2PAKOvercode-1,70-1,7
1901.08.2024564311MELKUN STROOPWAFELVLA-1PAKOvercode-2,450-2,45
2001.08.2024578057ALMHOF CHOCO KARAMEL SLAG-1CUPOvercode-1,380-1,38
2101.08.2024578366ZHOEVE PROT YOGH VANILLE-2CUPOvercode-3,540-3,54
2201.08.2024586171CAMPIN VLA SEIZ ZOMERFRUI-4PAKOvercode-7,960-7,96
2301.08.2024587964JUMBO CHOCOMOUSSE KOFFIE-1CUPOvercode-1,450-1,45
2401.08.2024588196JUMBO CHOCOMOUSSE ORIGIN-3CUPOvercode-4,350-4,35
2501.08.2024331532JUMBO VERSE SLAGROOM 35%-1CUPBreuk-1,390-1,39
2602.08.202465689OPTIME DRINK AARDB/KERS-4PAKOvercode-7,80-7,8
2702.08.2024198297OPTIME DRINK AARDBEI 1L-1STKOvercode-1,890-1,89
2802.08.2024356966JUMBO DRINKYOG 0% PERZ1,5-4PAKOvercode-7,360-7,36
2902.08.2024559073JUMBO VOLLE MELK 1.5L BLK-3PAKOvercode-5,760-5,76
3002.08.202431677CAMPIN HALFVOLLE MILDE YO-2PAKOvercode-3,280-3,28
3102.08.202479394MELKUN HAVERMOUTPAP-1CUPOvercode-1,390-1,39
3202.08.202497050CAMPIN ZACHT LUCHTIG CHOC-4PAKOvercode-9,40-9,4
3302.08.2024194964CAMPIN KWARK MAGER 1KG-2BAKOvercode-6,640-6,64
3402.08.2024347423CAMPIN KWARK MAGER AARDB-1CUPOvercode-1,990-1,99
3502.08.2024491422ACTIVI YOGH NATUREL 4PK-1TUBOvercode-2,270-2,27
3602.08.2024527094CAMPIN DUBBELVLA VAN/AARD-3PAKOvercode-6,870-6,87
3702.08.2024569938JUMBO AMANDELYOGHURT 400G-4STKOvercode-7,960-7,96
3802.08.2024578830DANOON TUSSENDOORTJE-4PAKOvercode-3,760-3,76
3902.08.2024578882DANONE M&M YOGHURT-2STKOvercode-2,260-2,26
4002.08.2024593943MELKUN PROT KWARK VANILLE-3CUPOvercode-4,470-4,47
4102.08.2024610577JUMBO SLAGROOM 35% 125ML-3CUPOvercode-2,970-2,97
4203.08.2024610413SCHARREL EI WIT 12ST M/L-1DSBreuk-2,550-2,55
Blad1


Now I need to format that information into this:
DateItem IDItem NameTypeAmountCost PerTotal Cost
Which is almost exactly the same, but this is on a different sheet and I need the date to be sorted into a date excel can understand basically. And all the lines need to be added. I understand that Item name is a vlookup but I am not even sure how to add the dates/itemids for all rows that are dynamic.

As of writing this I also find it very confusing for what I am asking so to avoid further confusion ill end it here and answer questions you have. I hope someone can help me out here for a bit.
(p.s. the date in the raw data looks sorted but it actually isn't. if I were to add 01.07.2024 dates to it then excel dont understand it anymore and u'll see july and august mixed)
Stuff should look like this eventually:
DateItem IDItem NameTypeAmountCost PerTotal Cost
01/08/2024602972JUMBO GEKLEURDE EIEREN 6Overcode9€ 22,41€ 201,69
01/08/2024602972JUMBO GEKLEURDE EIEREN 6Overcode10€ 22,41€ 224,10
01/08/2024602648JUMBO SCHARREL EI 6ST M/LBreuk1€ 2,99€ 2,99
This is putting raw data into a bit more refined raw data that makes it easier to work with I guess?

Thanks in advance,
Ramballah
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Power query
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Item ID", Int64.Type}, {"Item Name", type text}, {"Amount", Int64.Type}, {"Piece", type text}, {"Type", type text}, {"Item Cost", type number}, {"nvt", Int64.Type}, {"Total Cost", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Date", "Item ID", "Item Name", "Type", "Amount", "Item Cost", "Total Cost", "Piece", "nvt"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Piece", "nvt", "Total Cost"}),
    #"Calculated Absolute Value" = Table.TransformColumns(#"Removed Columns",{{"Amount", Number.Abs, Int64.Type}, {"Item Cost", Number.Abs, type number}}),
    #"Added Custom" = Table.AddColumn(#"Calculated Absolute Value", "Total Cost", each [Amount]*[Item Cost])
in
    #"Added Custom"

Book1
ABCDEFG
1DateItem IDItem NameTypeAmountItem CostTotal Cost
201/08/2024602972JUMBO GEKLEURDE EIEREN 6Overcode922.41201.69
301/08/2024602972JUMBO GEKLEURDE EIEREN 6Overcode1024.9249
401/08/2024602648JUMBO SCHARREL EI 6ST M/LBreuk12.992.99
501/08/2024352587JUMBO DRINKYOGH 0% BANAANOvercode56.4532.25
601/08/2024432527OPTIME DRINK BANAAN 1LOvercode11.991.99
701/08/2024526943CAMPIN FRUITMELK BANAANOvercode11.751.75
801/08/2024547425ACTIME KIDS DRINK AARDBEIOvercode12.342.34
901/08/2024559932CAMPIN VOLLE MELK 1,5LOvercode36.4519.35
1001/08/2024559932CAMPIN VOLLE MELK 1,5LOvercode36.4519.35
1101/08/2024102408MELKUN HAVERMOUTPAPOvercode12.192.19
1201/08/2024183639CAMPIN KWARK VOL 500GROvercode510.251
1301/08/2024206420CAMPIN VOLLE YOGHURT 1,5LOvercode12.492.49
1401/08/2024221284JUMBO BIOGARDE HV STANDYOOvercode56.9534.75
1501/08/2024304545JUMBO BOLLETJES VANILLEVLOvercode1119.69216.59
1601/08/2024308005MONA PUD FRAMBOOS MSOvercode512.3561.75
1701/08/2024369633OETKER PAULA VAN/CHOC 4PKOvercode12.592.59
1801/08/2024500806JUMBO BLK MAGER YOGH 0,5LOvercode21.73.4
1901/08/2024564311MELKUN STROOPWAFELVLAOvercode12.452.45
2001/08/2024578057ALMHOF CHOCO KARAMEL SLAGOvercode11.381.38
2101/08/2024578366ZHOEVE PROT YOGH VANILLEOvercode23.547.08
2201/08/2024586171CAMPIN VLA SEIZ ZOMERFRUIOvercode47.9631.84
2301/08/2024587964JUMBO CHOCOMOUSSE KOFFIEOvercode11.451.45
2401/08/2024588196JUMBO CHOCOMOUSSE ORIGINOvercode34.3513.05
2501/08/2024331532JUMBO VERSE SLAGROOM 35%Breuk11.391.39
2602/08/202465689OPTIME DRINK AARDB/KERSOvercode47.831.2
2702/08/2024198297OPTIME DRINK AARDBEI 1LOvercode11.891.89
2802/08/2024356966JUMBO DRINKYOG 0% PERZ1,5Overcode47.3629.44
2902/08/2024559073JUMBO VOLLE MELK 1.5L BLKOvercode35.7617.28
3002/08/202431677CAMPIN HALFVOLLE MILDE YOOvercode23.286.56
3102/08/202479394MELKUN HAVERMOUTPAPOvercode11.391.39
3202/08/202497050CAMPIN ZACHT LUCHTIG CHOCOvercode49.437.6
3302/08/2024194964CAMPIN KWARK MAGER 1KGOvercode26.6413.28
3402/08/2024347423CAMPIN KWARK MAGER AARDBOvercode11.991.99
3502/08/2024491422ACTIVI YOGH NATUREL 4PKOvercode12.272.27
3602/08/2024527094CAMPIN DUBBELVLA VAN/AARDOvercode36.8720.61
3702/08/2024569938JUMBO AMANDELYOGHURT 400GOvercode47.9631.84
3802/08/2024578830DANOON TUSSENDOORTJEOvercode43.7615.04
3902/08/2024578882DANONE M&M YOGHURTOvercode22.264.52
4002/08/2024593943MELKUN PROT KWARK VANILLEOvercode34.4713.41
4102/08/2024610577JUMBO SLAGROOM 35% 125MLOvercode32.978.91
4203/08/2024610413SCHARREL EI WIT 12ST M/LBreuk12.552.55
Table1
 
Upvote 0
Kerryx said:
Power query
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Item ID", Int64.Type}, {"Item Name", type text}, {"Amount", Int64.Type}, {"Piece", type text}, {"Type", type text}, {"Item Cost", type number}, {"nvt", Int64.Type}, {"Total Cost", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Date", "Item ID", "Item Name", "Type", "Amount", "Item Cost", "Total Cost", "Piece", "nvt"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Piece", "nvt", "Total Cost"}),
    #"Calculated Absolute Value" = Table.TransformColumns(#"Removed Columns",{{"Amount", Number.Abs, Int64.Type}, {"Item Cost", Number.Abs, type number}}),
    #"Added Custom" = Table.AddColumn(#"Calculated Absolute Value", "Total Cost", each [Amount]*[Item Cost])
in
    #"Added Custom"

Book1
ABCDEFG
1DateItem IDItem NameTypeAmountItem CostTotal Cost
201/08/2024602972JUMBO GEKLEURDE EIEREN 6Overcode922.41201.69
301/08/2024602972JUMBO GEKLEURDE EIEREN 6Overcode1024.9249
401/08/2024602648JUMBO SCHARREL EI 6ST M/LBreuk12.992.99
501/08/2024352587JUMBO DRINKYOGH 0% BANAANOvercode56.4532.25
601/08/2024432527OPTIME DRINK BANAAN 1LOvercode11.991.99
701/08/2024526943CAMPIN FRUITMELK BANAANOvercode11.751.75
801/08/2024547425ACTIME KIDS DRINK AARDBEIOvercode12.342.34
901/08/2024559932CAMPIN VOLLE MELK 1,5LOvercode36.4519.35
1001/08/2024559932CAMPIN VOLLE MELK 1,5LOvercode36.4519.35
1101/08/2024102408MELKUN HAVERMOUTPAPOvercode12.192.19
1201/08/2024183639CAMPIN KWARK VOL 500GROvercode510.251
1301/08/2024206420CAMPIN VOLLE YOGHURT 1,5LOvercode12.492.49
1401/08/2024221284JUMBO BIOGARDE HV STANDYOOvercode56.9534.75
1501/08/2024304545JUMBO BOLLETJES VANILLEVLOvercode1119.69216.59
1601/08/2024308005MONA PUD FRAMBOOS MSOvercode512.3561.75
1701/08/2024369633OETKER PAULA VAN/CHOC 4PKOvercode12.592.59
1801/08/2024500806JUMBO BLK MAGER YOGH 0,5LOvercode21.73.4
1901/08/2024564311MELKUN STROOPWAFELVLAOvercode12.452.45
2001/08/2024578057ALMHOF CHOCO KARAMEL SLAGOvercode11.381.38
2101/08/2024578366ZHOEVE PROT YOGH VANILLEOvercode23.547.08
2201/08/2024586171CAMPIN VLA SEIZ ZOMERFRUIOvercode47.9631.84
2301/08/2024587964JUMBO CHOCOMOUSSE KOFFIEOvercode11.451.45
2401/08/2024588196JUMBO CHOCOMOUSSE ORIGINOvercode34.3513.05
2501/08/2024331532JUMBO VERSE SLAGROOM 35%Breuk11.391.39
2602/08/202465689OPTIME DRINK AARDB/KERSOvercode47.831.2
2702/08/2024198297OPTIME DRINK AARDBEI 1LOvercode11.891.89
2802/08/2024356966JUMBO DRINKYOG 0% PERZ1,5Overcode47.3629.44
2902/08/2024559073JUMBO VOLLE MELK 1.5L BLKOvercode35.7617.28
3002/08/202431677CAMPIN HALFVOLLE MILDE YOOvercode23.286.56
3102/08/202479394MELKUN HAVERMOUTPAPOvercode11.391.39
3202/08/202497050CAMPIN ZACHT LUCHTIG CHOCOvercode49.437.6
3302/08/2024194964CAMPIN KWARK MAGER 1KGOvercode26.6413.28
3402/08/2024347423CAMPIN KWARK MAGER AARDBOvercode11.991.99
3502/08/2024491422ACTIVI YOGH NATUREL 4PKOvercode12.272.27
3602/08/2024527094CAMPIN DUBBELVLA VAN/AARDOvercode36.8720.61
3702/08/2024569938JUMBO AMANDELYOGHURT 400GOvercode47.9631.84
3802/08/2024578830DANOON TUSSENDOORTJEOvercode43.7615.04
3902/08/2024578882DANONE M&M YOGHURTOvercode22.264.52
4002/08/2024593943MELKUN PROT KWARK VANILLEOvercode34.4713.41
4102/08/2024610577JUMBO SLAGROOM 35% 125MLOvercode32.978.91
4203/08/2024610413SCHARREL EI WIT 12ST M/LBreuk12.552.55
Table1
Click to expand...
I think I am a bit dumb, but how do I add this power query? I dont understand power query at all. Sorry
 
Upvote 0
Some info here on how to use it , but its built into excel and great for handling data like this

www.mrexcel.com

VBA to transpose blocks (arrays) of data

I have to transform literally hundreds of blocks of data exported from financial software into a different format for my manager. The source data is on the left in the image and the required output is on the right. Doing it manually is not that difficult but i will drive me to drink! Any help...
www.mrexcel.com www.mrexcel.com
 
Upvote 0
Kerryx said:
Some info here on how to use it , but its built into excel and great for handling data like this

www.mrexcel.com

VBA to transpose blocks (arrays) of data

I have to transform literally hundreds of blocks of data exported from financial software into a different format for my manager. The source data is on the left in the image and the required output is on the right. Doing it manually is not that difficult but i will drive me to drink! Any help...
www.mrexcel.com www.mrexcel.com
Click to expand...
I managed to make it work. It works great. Just one thing isnt and its probably because i was unclear. All the minus numbers are now positive which is great but all the positive numbers need to be minus numbers and that isnt working right now. Can u maybe tell me how to make that work?
 
Upvote 0
What column or columns exactly have the negative and positive values?, i could only see negative in all, its a simple fix
 
Upvote 0
Remove the step Calculate Absolute value
1724162157730.png

Select the column you want to change example the amount column
1724162238483.png

Go to Transform -> Standard -> Multiply
1724162325223.png

Enter -1 then click on OK

Done
 

Attachments

  • 1724162217300.png
    1724162217300.png
    6.7 KB · Views: 6
Upvote 0
If you multiply by a negative 1 (-1) , the negative values in the column will turn positive and any positive values go negative.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Ramballah
  • Solved
Removing duplicates and adding amounts
2
Replies
18
Views
315
Fluff
Fluff
Ramballah
  • Solved
VLookup in Power Query?
2
Replies
16
Views
455
Ramballah
Ramballah

Forum statistics

Threads
1,221,467
Messages
6,160,018
Members
451,611
Latest member
PattiButche

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top