Raw data to compact excel chart

R

rameshppc

Board Regular
Joined
Jun 10, 2017
Messages
116
Office Version
  1. 2013
Platform
  1. Windows
Hi.. Good evening..
We have A to AC column data received from our buyer. We need to extract the column A,E & G data in the attached output format. Pls advise the suitable formula to achieve the result.

INPUT

Book2 - raw data to compact chart.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1PO NoPR NoArticleDescriptionsizeReq DtBOM QuantityPO ValueStyle NoPlantOTB NumberClusterFix vendor codeFix vendor nameSeasonSeason DescSeason YRFashion GrdPGrpBrandBrand NameFormatPO Delivery DateCreation StatusCurrencyMRP For VKP0M.C.DescriptionExchange RateItem Quantity
22000127806PR1684052434443065654002KG FRENDZ_T-SHIRTS_443065654002_WHITE, WHITE, 3-4Y3-4Y27-Feb1103.479599TSS25UKKGKF9773TDU4F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS11
32000127806PR1684052434443065654004KG FRENDZ_T-SHIRTS_443065654004_WHITE, WHITE, 7-8Y7-8Y27-Feb3310.438797TSS25UKKGKF9773TDU4F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS13
42000127806PR1684052434443065654003KG FRENDZ_T-SHIRTS_443065654003_WHITE, WHITE, 5-6Y5-6Y27-Feb3310.438797TSS25UKKGKF9773TDU4F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS13
52000127807PR4146527938443065654003KG FRENDZ_T-SHIRTS_443065654003_WHITE, WHITE, 5-6Y5-6Y27-Feb3310.438797TSS25UKKGKF97738062F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS13
62000127807PR4146527938443065654001KG FRENDZ_T-SHIRTS_443065654001_WHITE, WHITE, 2-3Y2-3Y27-Feb1103.479599TSS25UKKGKF97738062F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS11
72000127808PR9570409041443065654002KG FRENDZ_T-SHIRTS_443065654002_WHITE, WHITE, 3-4Y3-4Y27-Feb2206.959198TSS25UKKGKF97738067F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS12
82000127808PR9570409041443065654003KG FRENDZ_T-SHIRTS_443065654003_WHITE, WHITE, 5-6Y5-6Y27-Feb1103.479599TSS25UKKGKF97738067F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS11
92000127808PR9570409041443065654004KG FRENDZ_T-SHIRTS_443065654004_WHITE, WHITE, 7-8Y7-8Y27-Feb3310.438797TSS25UKKGKF97738067F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS13
102000127817PR2112186247443065654004KG FRENDZ_T-SHIRTS_443065654004_WHITE, WHITE, 7-8Y7-8Y27-Feb2206.959198TSS25UKKGKF9773TP21F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS12
112000127820PR6628594644443065654004KG FRENDZ_T-SHIRTS_443065654004_WHITE, WHITE, 7-8Y7-8Y27-Feb3357.0888062TSS25UKKGKF97731879F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS13
122000127821PR0083226835443065654004KG FRENDZ_T-SHIRTS_443065654004_WHITE, WHITE, 7-8Y7-8Y27-Feb1103.479599TSS25UKKGKF97731982F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS11
132000127821PR0083226835443065654002KG FRENDZ_T-SHIRTS_443065654002_WHITE, WHITE, 3-4Y3-4Y27-Feb2206.959198TSS25UKKGKF97731982F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS12
142000127821PR0083226835443065654001KG FRENDZ_T-SHIRTS_443065654001_WHITE, WHITE, 2-3Y2-3Y27-Feb1103.479599TSS25UKKGKF97731982F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS11
152000127822PR6675336571443065654004KG FRENDZ_T-SHIRTS_443065654004_WHITE, WHITE, 7-8Y7-8Y27-Feb1103.479599TSS25UKKGKF97731989F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS11
162000127822PR6675336571443065654003KG FRENDZ_T-SHIRTS_443065654003_WHITE, WHITE, 5-6Y5-6Y27-Feb1103.479599TSS25UKKGKF97731989F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS11
172000127822PR6675336571443065654002KG FRENDZ_T-SHIRTS_443065654002_WHITE, WHITE, 3-4Y3-4Y27-Feb1103.479599TSS25UKKGKF97731989F134560004APSP202507K56Tirupurhj456APSPApparel -Spring2025APL-UltimateK56F134KG FRENDZMid Economy25-03-2025 00:00H4INR199T SHIRTS11
INPUT
Cell Formulas
RangeFormula
E2:E17E2=RIGHT(D2,4)


OUTPUT

Book2 - raw data to compact chart.xlsx
ABCDEFGH
3S NOPO NOPLANTSIZETOTAL
42-3Y3-4Y5-6Y7-8Y
512000106961TG6N22228
622000107073TX6W11114
OUTPUT
Cell Formulas
RangeFormula
H5:H6H5=SUM(D5:G5)
 
Sort by date Sort by votes
I would do this with just few clicks in Power Query (Get and Transform Data)> If I remember in Excel 2013 (or may be your profile is outdated?) it was already a standard part of excel.

You can start with balnk query and insert such m code in advanced editor (remember to change folder tath to proper one) and then click Close and load.

Power Query: 
let
    Source = Excel.Workbook(File.Contents("C:\Users\Kaper\test\excelfora\MR_excel1270359.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Description", "PO No", "BOM Quantity", "Plant"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Size", each Text.End([Description],4)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Description"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"PO No", "Plant", "Size"}, {{"Count", each List.Sum([BOM Quantity]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Size]), "Size", "Count"),
    #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum({[#"3-4Y"], [#"7-8Y"], [#"5-6Y"], [#"2-3Y"]}), type number),
    #"Added Index" = Table.AddIndexColumn(#"Inserted Sum", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "PO No", "Plant", "3-4Y", "7-8Y", "5-6Y", "2-3Y", "Addition"})
in
    #"Reordered Columns"

The output shall look like that (for your sample data):
MR_excel1270359.xlsx
ABCDEFGH
1IndexPO NoPlant3-4Y7-8Y5-6Y2-3YAddition
212000127806TDU41337
3220001278078062314
43200012780880672316
542000127817TP2122
652000127820187933
76200012782119822114
87200012782219891113
Sheet2 (2)


So some formatting, may be column names change and columns sequence rearrangement (it is done at level of Power Query) - I left automatic names given by PQ.
 
Upvote 0
Hi Kaper...
I am really sorry to say that, i dont know about power query.
I am looking solution through excel functions and its easy to understand for me.
Pls dont mistake me..
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
VBA Copy, Paste, Match query
Replies
3
Views
792
ckdragon
C
Rob_010101
  • Question Question
Weekly meeting tracker
Replies
6
Views
445
JerrExcel
JerrExcel
N
  • Solved
Copy Multiple Ranges with Criteria Match Paste into Another Sheet with appropriate ranges
Replies
4
Views
698
nandhavnk
N
O
  • Solved
VBA for Data w/ Repeating Headers
Replies
9
Views
2K
jolivanes
jolivanes
J
  • Question Question
Excel function, exclude dates in chart data source, excel 2010
Replies
8
Views
561
VBA Geek
VBA Geek

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