Excel data extract for merge and assign currency row

Question

Hi I have a data set please find attached sample set. Where in I have used a macro to extract the data from Row 0 or row 3 (analyzing which will help). I want to use the currency for example USD and create a column currency for respective sections. Thanks for the help

Currency USD      Sales ReportWeeks   1234Week 1Sales112101023 Books210232344 Tools323444453 Others444535316Week 2Sales510231012 Books623442310 Tools744534423 Others853165344        Currency INR      Sales ReportWeeks   1234Week 1Sales123101023 Books244232344 Tools353444453 Others416535316Week 2Sales512231012 Books610442310 Tools723534423 Others844165344Week 3Sales912231012 Books1010442310 Tools1123534423 Others1244165344        Currency YEN      Sales ReportWeeks   1234Week 1Sales123101023 Books244232344 Tools353444453 Others416535316Week 2Sales512231012 Books610442310 Tools723534423 Others844165344Week 3Sales912231012 Books1010442310 Tools1123534423 Others1244165344Week 4Sales1312231012 Books1410442310 Tools1523534423 Others1644165344

Output           Sales1234Currency112101023USD210232344USD323444453USD444535316USD510231012USD623442310USD744534423USD853165344USD123101023INR244232344INR353444453INR416535316INR512231012INR610442310INR723534423INR844165344INR912231012INR1010442310INR1123534423INR1244165344INR123101023YEN244232344YEN353444453YEN416535316YEN512231012YEN610442310YEN723534423YEN844165344YEN912231012YEN1010442310YEN1123534423YEN1244165344YEN1312231012YEN1410442310YEN1523534423YEN1644165344YEN

Curr extract.xlsx

jrlindem · Answer

@Chaoued beat me to it 😀

But since I built out the workflow, here's my offering.  Mine has more tools as I am attempting to illustrate the concepts of how you can accomplish this as a bolt-on, after your macro processes upstream.  You can certainly combine steps to simplify though:

Workflow is also attached.  Hope this helps, -Jay

jrlindem_example.yxmd

Chaoued · Answer

@Lumjing  Here is a solution to your problem; however, you need to integrate it into your macro to obtain the expected output.

Spoiler

Community solution.yxmd