How should I tell the difference?

[CaptainCsaba]

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?

NORWAY--------------

12,700 ABC llc

RE HASPA MULTIINVEST

3 RUE DES LABOURg

.

.

L-1912

LUXEMBOURG

Beneficial Owner ref

Designation of LUXAI

(Ref: ABX89) 12,700

OE 26210;

HAHNSTRASSE 55

D-60528 FRANKFURT AM

GERMANY-------------

12,663 DEF llc

LEVEL 26

101 COLLINS STREET

MELBOURNE

VICTORIA

3000

AUSTRALIA

Beneficial Owner ref

(Ref: 19101) 12,663

GROSVENOR PLACE,

225 GEORGE STREET

SYDNEY NS 0001

AU------------------

12,581 GHI llc

11-3 HAMAMATSUCHO

2-CHOME, MINATO-KU

TOKY2

105-8579

JAPAN

Beneficial Owner ref

Designation of M7614

(Ref: EEU48) 12,581

MINATO-K=

TOKYO 1058579

JP------------------

12,306 IJK llc

2 OAKWOOD COURT

LITTLE OAK DRIVE

ANNESLEY

NOTTINGHAMSHIRE

NG15 DDR

UNITED KINGDOM

Beneficial Owner ref

(Ref: 45769) 12,306

MANAGEMENT LTD

31 GRESHAM STREET LO

EC2V 7QA UK---------

12,016 XYZ llc

Designation of JPMAG

(Ref: EBH83) 12,016

FRANKFURT AM MAIN

DE - FEDERAL REPUBLI

60329---------------

11,895 WCG llc

LANDSVAGEN 41

SUNDBYBER<

SE-172 6F

SWEDEN

Beneficial Owner ref

(Ref: BAP41) 11,895

BJORN MOLLER E62

SE-105 34 STOCKHOL:

<colgroup><col></colgroup><tbody>
</tbody>

 

[Dryver14]

does it always end llc?

 

[CaptainCsaba]

does it always end llc?

No unfortunately. Sometimes it's funds, sometimes it's peoples names, companies, hedge funds etc...

 

[thisoldman]

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?

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------------------

 

[CaptainCsaba]

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?

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------------------

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?

 

[Fluff]

If the line you want is always the line with info after something ending with ----, then try

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

 

[CaptainCsaba]

If the line you want is always the line with info after something ending with ----, then try

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

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.

 

[Fluff]

Could you give an example of that & what you need?

 

[CaptainCsaba]

Could you give an example of that & what you need?

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?

 

[Fluff]

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.

Can you please show some examples of this, highlighting the values that you want?