Data Extraction Reformatting from Excel

[k3pineapple]

Hi Team,

I would like to extract the table data from the sheet1 and sheet2 and merge them into one. Seeking your help to get this done, I have no idea how to start with this. Much appreciated for your kind help.

I have data file format as attached as input. (Page1_1,Page1_2)

The desired Output format: (as long as data can be extracted, i will be fine, if format is different)

Account Full Name (Account No)	Name of RLM RM:	Pledged Security	Code	Quantity	Exception Indicator	CCY	Value1	Value2 (ABC equiv.)	Inverse Floater	Pledging All Shares
*ABC - A/C 2  (*1111111)	K k k	ABC LTD (ABC GR)	1234567	8,000.0000		EUR	1,021,680.00	15,151,515.00
*ABC - A/C 2  (*1111111)	K k k	VOLA AA (VOW GR)	1234569	200.0000		EUR	295,000.00	444,415.00
*ABC - A/C 2  (*1111111)	K k k	WIR AA (WDI GY)	2345678	5,000.0000		EUR	390,000.00	515,151.00
*ABC - A/C 2  (*1111111)	K k k	YTAS AG (YT US)	ABC123NYC	20,000.0000		USD	33,000.00	15,151,515.00
*ABC - A/C 3  (*1111112)	K k A	ABC LTD (ABC GR)	1234567	8,000.0000		EUR	1,021,680.00	22,555.00
*ABC - A/C 3  (*1111112)	K k A	VOLA AA (VOW GR)	1234569	200.0000		EUR	295,000.00	2,323,332.00
*ABC - A/C 3  (*1111112)	K k A	WIR AA (WDI GY)	2345678	5,000.0000		EUR	390,000.00	232,323.00
*ABC - A/C 3  (*1111112)	K k A	YTAS AG (YT US)	ABC123NYC	20,000.0000		USD	33,000.00	6,666,666.00
KW LIMITED  (222333)		ABC HEALTH LTD (623 HK)	3353	50.0000		JPY	9,790.00	10,300.00
KW LIMITED  (222333)		YTT HOLDING LTD (12K US)	BBA03	5,000.0000		AUD	12,000.00	667.60
KW LIMITED  (222334)		ABC HEALTH LTD (623 HK)	3353	50.0000		JPY	9,790.00	10,300.00
KW LIMITED  (222334)		YTT HOLDING LTD (12K US)	BBA03	5,000.0000		AUD	12,000.00	667.60

[Yoshiro_Fujimori · Accepted solution]

Hi @k3pineapple 

Here is one way.

Depending on the variety of the sheet format, you may want to tweak the workflow.

Workflow

Macro

[k3pineapple]

@Yoshiro_Fujimori Much appreciated ！！！you really save my life.