text file to excel sheet problem(\s)

[mohadin]

Hi to you all,
Need macro to put the contents of text file in five columns (A-E) sheet, and sum 2nd column according to 3ed column (j&K)
I started the coding but:
1- The pattern I'm using works fine at the benign but in the end is messy couldn't sort it out.
2- the result I get is 1 dimension array , so how to convert it to fill 5 columns ?
Any way, the attached excel file may be clear, sheet 2 is the expected result and in sheet 1 is as far as I can get(messy at the end started in cell ("a1478")
please help
thanks

[AliGW]

This solution uses the PowerQuery add-in that is free to download here: https://www.microsoft.com/en-gb/down....aspx?id=39379

Import the data into PQ and transpose, then use the following tutorial to pivot the data (in your case, the modulo setting will be 6, not 5): https://excelgorilla.com/power-bi/po...-using-modulo/

Finally merge columns 3 and 4.

In Excel, set the date column to show month and year using custom formatting.

Note: the forum BB code does not like the Arabic, but it is correct in the attachment.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	0	1	Merged	4	5
2	7721	45000	1/????? ?????	Apr-19	5
3	395	36000	1/????? ?????	Apr-19	5
4	5436	33600	1/????? ?????	Apr-19	5
5	1998	60000	1/????? ?????	Apr-19	5
6	1525	30000	1/????? ?????	Apr-19	5
7	2468	48000	1/????? ?????	Apr-19	5
8	5480	27000	1/????? ?????	Apr-19	5
9	116	18000	1/????? ?????	Apr-19	5
10	8111	18000	1/????? ?????	Apr-19	5
11	7848	12000	1/????? ?????	Apr-19	5
12	7296	12000	1/????? ?????	Apr-19	5
13	5844	6000	1/????? ?????	Apr-19	5
14	1857	6000	1/????? ?????	Apr-19	5
15	3417	60000	1/????? ?????	Apr-19	5
16	1474	22500	1/????? ?????	Apr-19	5
17	7544	21000	1/????? ?????	Apr-19	5
18	5637	21000	1/????? ?????	Apr-19	5

Sheet: Sheet1

[mohadin]

Hi AliGW,
Thank you very much in deed
but in your attachment if you stroll down to the end!!!
not correct result!
thank you again

[AliGW]

Ah! Let's see what we can do about that ...

[jindon]

Please Login or Register  to view this content.

[mohadin]

Thank you AliGW

[AliGW]

The problem is the space in some of the entries in the column 3 entries.

Is there any chance of your getting the data in comma delimited format?

[mohadin]

Mr. Jindon
I Don't know what to say

Thank you as much as possible
In deed perfection As ever

[mohadin]

Hi AliGW
Thank you again
Answer to you Q is not possible
I get the text file from other party.

Thank you for your effort