SOLVED

Help to consolidate Data into one sheet.

Iron Contributor

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.

18 Replies

@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!=''

 

Screenshot_2023-10-15-08-53-10-218_com.microsoft.office.excel.jpg

best response confirmed by Hans Vogelaar (MVP)
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

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

Hi,

You seem to have a solution but is this excel ?
This is an online sql tool for Excel.
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.

@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)

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.

@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.

@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;

 

Screenshot_2023-10-18-08-57-47-701_cn.uujian.browser.jpg

@A_SIRAT 

 

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

@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.

@A_SIRAT 

 

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

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

It's end of the story... (lol)
Highly appreciated !
You're welcome. Take care

@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

This is very much welcomed.

Thank you again !
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
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

View solution in original post