How should I tell the difference?

C

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]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
In each block, the last word in each line beginning with "(Ref:" is the same as the first word in the line you are attempting to capture. Will this always be true?

Rich (BB code): 
12,700 ABC llc            <- MATCH 1
RE HASPA MULTIINVEST
3 RUE DES LABOURg
.
.
L-1912
LUXEMBOURG
Beneficial Owner ref
Designation of LUXAI
(Ref: ABX89) 12,700       <- MATCH 1
OE 26210;
HAHNSTRASSE 55
D-60528 FRANKFURT AM
GERMANY-------------
12,663 DEF llc            <- MATCH 2
LEVEL 26
101 COLLINS STREET
MELBOURNE
VICTORIA
3000
AUSTRALIA
Beneficial Owner ref
(Ref: 19101) 12,663       <- MATCH 2
GROSVENOR PLACE,
225 GEORGE STREET
SYDNEY NS 0001
AU------------------
12,581 GHI llc            <- MATCH 3
11-3 HAMAMATSUCHO
2-CHOME, MINATO-KU
TOKY2
105-8579
JAPAN
Beneficial Owner ref
Designation of M7614
(Ref: EEU48) 12,581       <- MATCH 3
MINATO-K=
TOKYO 1058579
JP------------------
 
Upvote 0
thisoldman said:
In each block, the last word in each line beginning with "(Ref:" is the same as the first word in the line you are attempting to capture. Will this always be true?

Rich (BB code): 
12,700 ABC llc            <- MATCH 1
RE HASPA MULTIINVEST
3 RUE DES LABOURg
.
.
L-1912
LUXEMBOURG
Beneficial Owner ref
Designation of LUXAI
(Ref: ABX89) 12,700       <- MATCH 1
OE 26210;
HAHNSTRASSE 55
D-60528 FRANKFURT AM
GERMANY-------------
12,663 DEF llc            <- MATCH 2
LEVEL 26
101 COLLINS STREET
MELBOURNE
VICTORIA
3000
AUSTRALIA
Beneficial Owner ref
(Ref: 19101) 12,663       <- MATCH 2
GROSVENOR PLACE,
225 GEORGE STREET
SYDNEY NS 0001
AU------------------
12,581 GHI llc            <- MATCH 3
11-3 HAMAMATSUCHO
2-CHOME, MINATO-KU
TOKY2
105-8579
JAPAN
Beneficial Owner ref
Designation of M7614
(Ref: EEU48) 12,581       <- MATCH 3
MINATO-K=
TOKYO 1058579
JP------------------
Click to expand...



It is true, but the number is not always the same. A lot fo times it gets divided if there are multiple managers eg (It can get divided up to 20 managers at times):

12,581 GHI llc
11-3 HAMAMATSUCHO
2-CHOME, MINATO-KU
TOKY2
105-8579
JAPAN
Beneficial Owner ref
Designation of M7614
(Ref: EEU48) 11,000
MINATO-K=
TOKYO 1058579
(Ref: ABC12) 1,581
LOMBARD STREET 21

What is your thinking on this? What is your idea about what could be the solution?
 
Upvote 0
If the line you want is always the line with info after something ending with ----, then try
Code: 
Sub GetGata()

   Dim Rng As Range
   Dim Cl As Range
   
   With Range("B1", Range("A" & Rows.Count).End(xlUp).Offset(, 1))
      .Value = Evaluate(Replace("if(right(@,4)=""----"",true,"""")", "@", .Offset(, -1).Address))
      For Each Rng In .SpecialCells(xlBlanks).Areas
         For Each Cl In Rng
            If Len(Application.Clean(Cl.Offset(, -1))) > 4 Then
               Cl.Value = Cl.Offset(, -1).Value
               Exit For
            End If
         Next Cl
      Next Rng
      .Replace True, "", xlWhole, , False, , False, False
      .SpecialCells(xlConstants).Copy Range("D1")
      .ClearContents
   End With

End Sub
 
Upvote 0
Fluff said:
If the line you want is always the line with info after something ending with ----, then try
Code: 
Sub GetGata()

   Dim Rng As Range
   Dim Cl As Range
   
   With Range("B1", Range("A" & Rows.Count).End(xlUp).Offset(, 1))
      .Value = Evaluate(Replace("if(right(@,4)=""----"",true,"""")", "@", .Offset(, -1).Address))
      For Each Rng In .SpecialCells(xlBlanks).Areas
         For Each Cl In Rng
            If Len(Application.Clean(Cl.Offset(, -1))) > 4 Then
               Cl.Value = Cl.Offset(, -1).Value
               Exit For
            End If
         Next Cl
      Next Rng
      .Replace True, "", xlWhole, , False, , False, False
      .SpecialCells(xlConstants).Copy Range("D1")
      .ClearContents
   End With

End Sub
Click to expand...

I thought about this one, the line thing would be a good idea, but if the "line" is directly at a page break it does not appear between the two shareholders. It happens a few time in each report.
 
Upvote 0
Could you give an example of that & what you need?
 
Upvote 0
Fluff said:
Could you give an example of that & what you need?
Click to expand...

To have a list in the end of the things I made "red".

12,700 ABC llc
12,663 DEF llc
12,581 GHI llc...

I may have confused you guys with the llc-s. It was only to make things easier. Instead of ABC llc a lot of thing could appear like Sir John Smith, JP Morgan Global Equity Fund, you get the point.

To be honest I have been thinking and realised that there is no real solution here. What we could do is to leave everything that is below 1,000 and basically make the macro offset everything by 1 cell to the right that look like "number,number text".
This will not include the things that are below 1000 but it will still make things a lot faster. I think we also need to make it a bit more complex because things that are above 999,999 will look lik "number,number,number text" and so on. Is it possible to do that?
 
Upvote 0
in post#7 you said
I thought about this one, the line thing would be a good idea, but if the "line" is directly at a page break it does not appear between the two shareholders. It happens a few time in each report.
Click to expand...
Can you please show some examples of this, highlighting the values that you want?
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top