CaptainCsaba
Board Regular
- Joined
- Dec 8, 2017
- Messages
- 78
Hey Everyone!
I need to create a macro to make come reports easier to handle. One of them looks like this when it gets put into excel. Basically it tells who the owners are to certain stocks. I need only the rows where it tells the stock owners only. A cell like that looks like this for example: 123,456 XYZ llc.
The number is the share amount and after that is the owner. I would need to get these lines. The problems is that there are cells below this which tell the adress, eg 555 Baker street.
I have no idea on what to write to distinguish these. I thought about using the "," as a reference but ten everything that is below 1,000 does not get added. Another problem si that when it gets converted from the PDF file it will not always be perfect,so sometimes weird symbols will get added for example and basically it will not be like "the needed cell is every 5th in a line" or "it is always 3 cells below a cell that looks like this". So I am kinda stuck here. I'll leave an example below ( i had to change the names for confidentiality). The lines we need are red. How should I solve this?
[TABLE="width: 178"]
<colgroup><col></colgroup><tbody>[TR]
[TD]NORWAY--------------[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]12,700 ABC llc[/TD]
[/TR]
[TR]
[TD]RE HASPA MULTIINVEST[/TD]
[/TR]
[TR]
[TD]3 RUE DES LABOURg[/TD]
[/TR]
[TR]
[TD].[/TD]
[/TR]
[TR]
[TD].[/TD]
[/TR]
[TR]
[TD]L-1912[/TD]
[/TR]
[TR]
[TD]LUXEMBOURG[/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref[/TD]
[/TR]
[TR]
[TD]Designation of LUXAI[/TD]
[/TR]
[TR]
[TD](Ref: ABX89) 12,700[/TD]
[/TR]
[TR]
[TD]OE 26210;[/TD]
[/TR]
[TR]
[TD]HAHNSTRASSE 55[/TD]
[/TR]
[TR]
[TD]D-60528 FRANKFURT AM[/TD]
[/TR]
[TR]
[TD]GERMANY-------------[/TD]
[/TR]
[TR]
[TD]12,663 DEF llc[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]LEVEL 26[/TD]
[/TR]
[TR]
[TD]101 COLLINS STREET[/TD]
[/TR]
[TR]
[TD]MELBOURNE[/TD]
[/TR]
[TR]
[TD]VICTORIA[/TD]
[/TR]
[TR]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD]AUSTRALIA[/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref[/TD]
[/TR]
[TR]
[TD](Ref: 19101) 12,663[/TD]
[/TR]
[TR]
[TD]GROSVENOR PLACE,[/TD]
[/TR]
[TR]
[TD]225 GEORGE STREET[/TD]
[/TR]
[TR]
[TD]SYDNEY NS 0001[/TD]
[/TR]
[TR]
[TD]AU------------------[/TD]
[/TR]
[TR]
[TD]12,581 GHI llc[/TD]
[/TR]
[TR]
[TD]11-3 HAMAMATSUCHO[/TD]
[/TR]
[TR]
[TD]2-CHOME, MINATO-KU[/TD]
[/TR]
[TR]
[TD]TOKY2[/TD]
[/TR]
[TR]
[TD]105-8579[/TD]
[/TR]
[TR]
[TD]JAPAN[/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref[/TD]
[/TR]
[TR]
[TD]Designation of M7614[/TD]
[/TR]
[TR]
[TD](Ref: EEU48) 12,581[/TD]
[/TR]
[TR]
[TD]MINATO-K=[/TD]
[/TR]
[TR]
[TD]TOKYO 1058579[/TD]
[/TR]
[TR]
[TD]JP------------------[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]12,306 IJK llc[/TD]
[/TR]
[TR]
[TD]2 OAKWOOD COURT[/TD]
[/TR]
[TR]
[TD]LITTLE OAK DRIVE[/TD]
[/TR]
[TR]
[TD]ANNESLEY[/TD]
[/TR]
[TR]
[TD]NOTTINGHAMSHIRE[/TD]
[/TR]
[TR]
[TD]NG15 DDR[/TD]
[/TR]
[TR]
[TD]UNITED KINGDOM[/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref[/TD]
[/TR]
[TR]
[TD](Ref: 45769) 12,306[/TD]
[/TR]
[TR]
[TD]MANAGEMENT LTD[/TD]
[/TR]
[TR]
[TD]31 GRESHAM STREET LO[/TD]
[/TR]
[TR]
[TD]EC2V 7QA UK---------[/TD]
[/TR]
[TR]
[TD]12,016 XYZ llc[/TD]
[/TR]
[TR]
[TD]Designation of JPMAG[/TD]
[/TR]
[TR]
[TD](Ref: EBH83) 12,016[/TD]
[/TR]
[TR]
[TD]FRANKFURT AM MAIN[/TD]
[/TR]
[TR]
[TD]DE - FEDERAL REPUBLI[/TD]
[/TR]
[TR]
[TD]60329---------------[/TD]
[/TR]
[TR]
[TD]11,895 WCG llc[/TD]
[/TR]
[TR]
[TD]LANDSVAGEN 41[/TD]
[/TR]
[TR]
[TD]SUNDBYBER<[/TD]
[/TR]
[TR]
[TD]SE-172 6F[/TD]
[/TR]
[TR]
[TD]SWEDEN[/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref[/TD]
[/TR]
[TR]
[TD](Ref: BAP41) 11,895[/TD]
[/TR]
[TR]
[TD]BJORN MOLLER E62[/TD]
[/TR]
[TR]
[TD]SE-105 34 STOCKHOL:[/TD]
[/TR]
</tbody>[/TABLE]
I need to create a macro to make come reports easier to handle. One of them looks like this when it gets put into excel. Basically it tells who the owners are to certain stocks. I need only the rows where it tells the stock owners only. A cell like that looks like this for example: 123,456 XYZ llc.
The number is the share amount and after that is the owner. I would need to get these lines. The problems is that there are cells below this which tell the adress, eg 555 Baker street.
I have no idea on what to write to distinguish these. I thought about using the "," as a reference but ten everything that is below 1,000 does not get added. Another problem si that when it gets converted from the PDF file it will not always be perfect,so sometimes weird symbols will get added for example and basically it will not be like "the needed cell is every 5th in a line" or "it is always 3 cells below a cell that looks like this". So I am kinda stuck here. I'll leave an example below ( i had to change the names for confidentiality). The lines we need are red. How should I solve this?
[TABLE="width: 178"]
<colgroup><col></colgroup><tbody>[TR]
[TD]NORWAY--------------[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]12,700 ABC llc[/TD]
[/TR]
[TR]
[TD]RE HASPA MULTIINVEST[/TD]
[/TR]
[TR]
[TD]3 RUE DES LABOURg[/TD]
[/TR]
[TR]
[TD].[/TD]
[/TR]
[TR]
[TD].[/TD]
[/TR]
[TR]
[TD]L-1912[/TD]
[/TR]
[TR]
[TD]LUXEMBOURG[/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref[/TD]
[/TR]
[TR]
[TD]Designation of LUXAI[/TD]
[/TR]
[TR]
[TD](Ref: ABX89) 12,700[/TD]
[/TR]
[TR]
[TD]OE 26210;[/TD]
[/TR]
[TR]
[TD]HAHNSTRASSE 55[/TD]
[/TR]
[TR]
[TD]D-60528 FRANKFURT AM[/TD]
[/TR]
[TR]
[TD]GERMANY-------------[/TD]
[/TR]
[TR]
[TD]12,663 DEF llc[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]LEVEL 26[/TD]
[/TR]
[TR]
[TD]101 COLLINS STREET[/TD]
[/TR]
[TR]
[TD]MELBOURNE[/TD]
[/TR]
[TR]
[TD]VICTORIA[/TD]
[/TR]
[TR]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD]AUSTRALIA[/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref[/TD]
[/TR]
[TR]
[TD](Ref: 19101) 12,663[/TD]
[/TR]
[TR]
[TD]GROSVENOR PLACE,[/TD]
[/TR]
[TR]
[TD]225 GEORGE STREET[/TD]
[/TR]
[TR]
[TD]SYDNEY NS 0001[/TD]
[/TR]
[TR]
[TD]AU------------------[/TD]
[/TR]
[TR]
[TD]12,581 GHI llc[/TD]
[/TR]
[TR]
[TD]11-3 HAMAMATSUCHO[/TD]
[/TR]
[TR]
[TD]2-CHOME, MINATO-KU[/TD]
[/TR]
[TR]
[TD]TOKY2[/TD]
[/TR]
[TR]
[TD]105-8579[/TD]
[/TR]
[TR]
[TD]JAPAN[/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref[/TD]
[/TR]
[TR]
[TD]Designation of M7614[/TD]
[/TR]
[TR]
[TD](Ref: EEU48) 12,581[/TD]
[/TR]
[TR]
[TD]MINATO-K=[/TD]
[/TR]
[TR]
[TD]TOKYO 1058579[/TD]
[/TR]
[TR]
[TD]JP------------------[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]12,306 IJK llc[/TD]
[/TR]
[TR]
[TD]2 OAKWOOD COURT[/TD]
[/TR]
[TR]
[TD]LITTLE OAK DRIVE[/TD]
[/TR]
[TR]
[TD]ANNESLEY[/TD]
[/TR]
[TR]
[TD]NOTTINGHAMSHIRE[/TD]
[/TR]
[TR]
[TD]NG15 DDR[/TD]
[/TR]
[TR]
[TD]UNITED KINGDOM[/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref[/TD]
[/TR]
[TR]
[TD](Ref: 45769) 12,306[/TD]
[/TR]
[TR]
[TD]MANAGEMENT LTD[/TD]
[/TR]
[TR]
[TD]31 GRESHAM STREET LO[/TD]
[/TR]
[TR]
[TD]EC2V 7QA UK---------[/TD]
[/TR]
[TR]
[TD]12,016 XYZ llc[/TD]
[/TR]
[TR]
[TD]Designation of JPMAG[/TD]
[/TR]
[TR]
[TD](Ref: EBH83) 12,016[/TD]
[/TR]
[TR]
[TD]FRANKFURT AM MAIN[/TD]
[/TR]
[TR]
[TD]DE - FEDERAL REPUBLI[/TD]
[/TR]
[TR]
[TD]60329---------------[/TD]
[/TR]
[TR]
[TD]11,895 WCG llc[/TD]
[/TR]
[TR]
[TD]LANDSVAGEN 41[/TD]
[/TR]
[TR]
[TD]SUNDBYBER<[/TD]
[/TR]
[TR]
[TD]SE-172 6F[/TD]
[/TR]
[TR]
[TD]SWEDEN[/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref[/TD]
[/TR]
[TR]
[TD](Ref: BAP41) 11,895[/TD]
[/TR]
[TR]
[TD]BJORN MOLLER E62[/TD]
[/TR]
[TR]
[TD]SE-105 34 STOCKHOL:[/TD]
[/TR]
</tbody>[/TABLE]