Help to consolidate Data into one sheet.

[A_SIRAT]

Hi,

I have attached an example file and would really appreciate your help.

I have explained what I want on the last sheet named consolidated data.

thanks in advance.

[#2]

@A_SIRAT 

How about this?

 https://b23.tv/PYAnjYh 

create temp table aa as 
select udf_fillna_m(iif(f07 like 'Week:',f08,''),'x') Week,udf_fillna_m(iif(f07 like 'Order nr:',f08,''),'y') Order_nr,udf_fillna_m( iif(f07 like 'Customer:',f08,''),'z') customer, iif((f03 like '-') or (f03 is null),'',f03) f03,iif((f03 like '-') or (f03 is null),'',f04) f04,iif((f05 like '-') or (f05 is null),'',f05) f05,iif((f05 like '-') or (f05 is null),'',f06) f06,iif((f07 like '-') or (f07 is null) or ( regexp('Week|Order nr|Customer',f07)),'',f07) f07,iif((f07 like '-') or (f07 is null) or (regexp('Week|Order nr|Customer',f07)),'',f08) f08 from consolidateSheet;
select Week,Order_nr,customer,f04 Variety_Name,f03 Quantity from aa where f03!='' union all 
select Week,Order_nr,customer,f06,f05 from aa where f05!='' union all 
select Week,Order_nr,customer,f08 ,f07  from aa where f07!=''

[#3 · Accepted solution]

Hi @A_SIRAT 

With Get & Transform aka Power Query. Assumed your Order sheets are named 1, 2, 3... 70 as in the file you shared

[#4]

Thank you !.
This has done exactly what I want.
Would you mind to share the steps then I will do more practice.

[#5]

Hi,

You seem to have a solution but is this excel ?

[#6]

This is an online sql tool for Excel.

[#7]

I thought I would get a chance to mark it as Best Response since I started the discussion. Anyway, I would have done it again....was just waiting for your second feedback.

[#8]

@A_SIRAT 

Would you mind to share the steps then I will do more practice

Which step(s) causes you a challenge?

(Power Query doc. is available here)

[#9]

Let me first dive into Power query to learn as a beginner as I found it very helpful. Thanks for the template you prepared. It has solved my problem.

[#10]

@L z. 

With the help of Power Query, would you be able to do further consolidation as explained in the attached sheet.

Thanks in advance.

[#11]

@A_SIRAT 

create temp table aa as 
select udf_fillna_m(iif(f07 like 'Week:',f08,''),'x') Week,udf_fillna_m(iif(f07 like 'Order nr:',f08,''),'y') Order_nr,udf_fillna_m( iif(f07 like 'Customer:',f08,''),'z') customer, iif((f03 like '-') or (f03 is null),'',f03) f03,iif((f03 like '-') or (f03 is null),'',f04) f04,iif((f05 like '-') or (f05 is null),'',f05) f05,iif((f05 like '-') or (f05 is null),'',f06) f06,iif((f07 like '-') or (f07 is null) or ( regexp('Week|Order nr|Customer',f07)),'',f07) f07,iif((f07 like '-') or (f07 is null) or (regexp('Week|Order nr|Customer',f07)),'',f08) f08 from consolidateSheet;
create temp table bb as 
select Order_nr,Week,customer,f04 Variety_Name,f03 Quantity  from aa where f03!='' union all 
select Order_nr,Week,customer,f06,f05 from aa where f05!='' union all 
select Order_nr,Week,customer,f08 ,f07  from aa where f07!='';
create temp table cc as 
select rowid old_rowid,*,replace(Quantity,',','')/200 `200`,(replace(Quantity,',','')%200)/100 `100` from bb;
//select * from cc;
cli_one_dim~temp.cc~6;
select colIndex[1:5],数量 `Labels To Print
`,属性 `Qty on Label` from ccunion order by old_rowid;

 

[#12]

https://b23.tv/9DdSkfG

[#13]

@A_SIRAT 

See attached file (added a couple of dummy values in Order sheet 1). Looks good to me but double-check please

[#14]

@L z. 

Thank you very much. I have done a few tests and it works perfectly :)

Would you be so kind and help me with the last request. I have just thrown a spanner in the works by adding a column named Master Data where I have indicated which varieties are divisible by 200 and others by 100.

[#15]

@A_SIRAT 

Looks like a never ending story... (lol)

Check attached file where I changed a few numbers in some Order sheets

[#16]

It's end of the story... (lol)
Highly appreciated !

[#17]

You're welcome. Take care

[#18]

@A_SIRAT 

On reflection - although I have no idea if you need this - I updated the formula for Total [Quantity] on 'Consolidation' so it works with filtered data

Hope this helps

[#19]

This is very much welcomed.

Thank you again !