reverse pivot table data / system gerenated report through macro or formula

[Ateeb Ali]

Dear Excel Ninjas
I need one help because I tried hard but not succeed at my end, I got system report in excel which haver merged cells and data in a table (like pivot table).
Attached excel file - sheet: Original Format

I wanted to copy data in some report so for copying, I need it in proper format as file attached - sheet: Required Format

Please someone help and guide, I need to apply it for several files in a day

 

[Marc L]

I need to apply it for several files in a day

So well describe, detail, elaborate, explain whatever in order there is nothing to guess, how it should work, how you should use it ? …​

​

 

[Ateeb Ali]

Dear Sir
I import this report from system, system generate several DCs in this format (file attached - system format).
I have highlighted top headings which can be different on Dcs

Example file also attached as DC#2

I need a report merging those Dcs, so final result file also attaching, my idea was to record any macro and I will apply it on every file to get data in one table then I will summaries all data in one sheet.

I hope - I have explained now.

 

[Marc L]

• Your idea to 'record any macro' is good so what have you done ?
  
  
• Data in your initial post attachment do not start in the same row # than in your last attachment : so weird, impossible to guess ‼
  
  
• What is the purpose of your attachment 'System Format.xlsb' ?
  
  
• All DC source workbooks are located alone in the same folder ?

​

Your post #3 is very different than your first post : how any helper can guess this ?‼​

​

As a reminder this is an Excel forum so very not a mind readers one !​

Well elaborate each step as if I post an one shot VBA demonstration​

you will have to fit it yourself for what you misexplained / forgot …​

​

 

[Ateeb Ali]

Sir, I am sorry I am not good with it
I have mentioned that I need any formula or vb code, i don't learn macros so don't know to use it.
Previously this forum helped me every time.

I received DC files by email with several attachment, I can copy it to one folder without problem, but to summaries it, I need to do many manual works like unmerge cells then copy data of PO, COLOR, CONES, then manally write column 1 , column 2 and column 3 against each line

 

[Marc L]

system generate several DCs in this format

So the easy way is to ask the system to generate workbooks compliant with the obvious easy Excel rules …​

​

 

[Ateeb Ali]

System administrator already reduced because it serves the purpose of other department but not to me.
Anyways thanks for your help, I will try to do it with "IF" condition to copy headers

 

[Marc L]

As the initial attachment has not the same format as your Example File DC-2 workbook​

so for checking & testing purpose attach another source data binary format workbook …​

​

 

[Ateeb Ali]

sir, attaching original system generated excel binary report files, I just need to combine this data in line format
Example file also attached where I just convert "U02 P-MTI2300002"

 

[p45cal]

Are there any other formats the data can be exported as?
Ask you system administrator for a list of formats. List them here. Excel is able to import many, many formats, and more robustly than the awful layouts you're currently getting.

 

[Ateeb Ali]

attaching How I received reports via email
yes it is same original format, system administrator will not help me because it is for all departments
so only for me, he will not amend.

I have also record macro and my 90% work is now done, need help to further improve the code if possible
press alt+f11 then you can run the macro

 

[Marc L]

According to your post #9 attachment, the layout format is not consistant between workbooks - 4 workbooks : 3 differents formats ! -​

so you should - must ! - ask to have always the same unique format …​

​

 

[Ateeb Ali]

sir the format always different as per DC data

 

[Marc L]

In this case if I post a VBA demonstration according to your post #9 attachment​

but as an one shot 'cause I won't enter any never ending story each time there is a new layout format​

so you will have to modify the code yourself to fit each new case so this is time to learn VBA !​

​

The VBA demonstration won't work with your initial post attachment neither with your post #11 attachment​

but maybe your post #9 attachment does not well reflect again your real source files ?​

​

According to such poor worksheet design I won't go for the fastest way​

but for the slow classic Excel & VBA basics at beginner level so easier to maintain by yourself.​

​

Without learning VBA it's time to ask your boss for an unique format as actually that's very not professional​

and the guy generating the workbooks must do a better job to fit your need …​

​

 

[Ateeb Ali]

In this case if I post a VBA demonstration according to your post #9 attachment

How can I see this demonstration?
I have not find any attachment

 

[Marc L]

Well read my previous post …​

​