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]
 
Sort by date Sort by votes
Fluff said:
in post#7 you said
Can you please show some examples of this, highlighting the values that you want?
Click to expand...

We get these as PDF-s. Adobe acrobat converts them to excel and we have something like this in the end:

[TABLE="width: 504"]
<colgroup><col></colgroup><tbody>[TR]
[TD]. [/TD]
[/TR]
[TR]
[TD]3,223,969 THE JUPITE R GLOBAL FUND S ICAV [/TD]
[/TR]
[TR]
[TD]6 ROUTE DE TREVEg [/TD]
[/TR]
[TR]
[TD]SENNINGERBER< [/TD]
[/TR]
[TR]
[TD]L-2633 [/TD]
[/TR]
[TR]
[TD]LUXEMBOURG [/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref : 111289 [/TD]
[/TR]
[TR]
[TD]Designation of LUXUC Ip [/TD]
[/TR]
[TR]
[TD](Ref: ACE67) 3,142,1 87 JUPITE R AS SE T MA NAGEMENT [/TD]
[/TR]
[TR]
[TD]THE ZIG ZAG, 70 VICT ORIA STRE ET [/TD]
[/TR]
[TR]
[TD]LONDON SW1E 6SQ [/TD]
[/TR]
[TR]
[TD]UNITED KINGDOM [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD](Ref: AXZ33) 80,851 JUPITER A SSET M ANAG EMENT [/TD]
[/TR]
[TR]
[TD]THE ZIG ZAG, 70 VICT ORIA STRE ET [/TD]
[/TR]
[TR]
[TD]LONDON SW1E 6SQ [/TD]
[/TR]
[TR]
[TD]UNITED KINGDO: [/TD]
[/TR]
[TR]
[TD](Ref: ECG69) 931 6 R OUTE DE T REVE S [/TD]
[/TR]
[TR]
[TD]SENNINGERBERG [/TD]
[/TR]
[TR]
[TD]LU - LUXEMBOURG [/TD]
[/TR]
[TR]
[TD]L-2633-------------- --------- ---- -- ---- ---------------------------------------" [/TD]
[/TR]
[TR]
[TD]2,372,777 VANGUARD T OTAL INTE RNAT IO NAL STOC& [/TD]
[/TR]
[TR]
[TD]INDEX FUND [/TD]
[/TR]
[TR]
[TD]100 VANGUARD BOULEVA Rr [/TD]
[/TR]
[TR]
[TD]MALVERN [/TD]
[/TR]
[TR]
[TD]PENNSYLVANIA [/TD]
[/TR]
[TR]
[TD]19355 [/TD]
[/TR]
[TR]
[TD]U.S.A [/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref : 101961 [/TD]
[/TR]
[TR]
[TD]Designation of BBHLE Nr [/TD]
[/TR]
[TR]
[TD](Ref: 51576) 2,372,7 77 THE VA NGUA RD GRO UP INC [/TD]
[/TR]
[TR]
[TD]PO BOX 1102 MAILSTOP A29 [/TD]
[/TR]
[TR]
[TD]VALLEY FORGE PA [/TD]
[/TR]
[TR]
[TD]19355 UNITED STATES [/TD]
[/TR]
[TR]
[TD]ATTN: CHRISTOPHER WI GHTMAN--- ---- -- ---- -----------------------------------------------------------" [/TD]
[/TR]
[TR]
[TD]1,546,230 TAMESIDE M BC RE GRE ATER M ANCH ESTER [/TD]
[/TR]
[TR]
[TD]PENSION FUND [/TD]
[/TR]
[TR]
[TD]5 MANCHESTER ROAr [/TD]
[/TR]
[TR]
[TD]DROYLSDEN [/TD]
[/TR]
[TR]
[TD]GREATER MANCHESTER [/TD]
[/TR]
[TR]
[TD]M43 6SF [/TD]
[/TR]
[TR]
[TD]UNITED KINGDOM [/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref : 76657 [/TD]
[/TR]
[TR]
[TD]Designation of TMBC$ [/TD]
[/TR]
[TR]
[TD](Ref: 13465) 1,546,2 30 UBS AS SET MA NAGE MENT [/TD]
[/TR]
[TR]
[TD]21 LOMBARD STREET [/TD]
[/TR]
[TR]
[TD]LONDON EC3V 9AH [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] (a page break was here, the "line does not appear -CaptainCsaba) [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]1,353,255 JUPITER EU ROPEAN OP PORT UN ITIE S [/TD]
[/TR]
[TR]
[TD]TRUST PLC [/TD]
[/TR]
[TR]
[TD]THE ZIG ZAG BUILDING [/TD]
[/TR]
[TR]
[TD]70 VICTORIA STREET [/TD]
[/TR]
[TR]
[TD]LONDOj [/TD]
[/TR]
[TR]
[TD]SW1E 6SQ [/TD]
[/TR]
[TR]
[TD]UNITED KINGDOM [/TD]
[/TR]
[TR]
[TD]Beneficial Owner ref : 110718 [/TD]
[/TR]
[TR]
[TD]Designation of JPMEL AI) [/TD]
[/TR]
[TR]
[TD](Ref: ABN97) 1,353,2 55 JUPITE R AS SE T MA NAGEMENT [/TD]
[/TR]
[TR]
[TD]THE ZIG ZAG, 70 VICT ORIA STRE ET [/TD]
[/TR]
[TR]
[TD]LONDON SW1E 6SQ [/TD]
[/TR]
</tbody>[/TABLE]


Every line is basically one big cell, so what you see here is only the "A" column. What I want is the red cells to get put into The B column next to them. So if the "3,223,969 THE JUPITE R GLOBAL FUND S ICAV" is in A33, then it should be moved into B33. What makes this hard is that there are alot of similar cells. For example "70 VICTORIA STREET" is the same format, the only difference is that it does not have an "," in it. So I guess we should use if we don't have a better idea.
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Give this a go, it will probably need some further tweaks
Code: 
Sub GetData()

   Dim Rng As Range
   Dim Cl As Range
   
   With Range("B1", Range("A" & Rows.Count).End(xlUp).Offset(, 1))
      .Value = Evaluate(Replace("if(isnumber(value(left(@,find("" "",@)-1))),@,"""")", "@", .Offset(, -1).Address))
      For Each Rng In .SpecialCells(xlConstants).Areas
         For Each Cl In Rng
            If InStr(1, Cl.Value, ",") = 0 Then Cl.ClearContents
         Next Cl
      Next Rng
      .SpecialCells(xlConstants).Copy Range("D1")
      .ClearContents
   End With

End Sub
 
Upvote 0
Thank you for the help! I left the office for the week, but I will give it a try on mondy morning. I will write the results. Thank you for the help again!
 
Upvote 0
Glad to help & have a good weekend.

Look forward to hearing the results, but will probably need a few tweaks.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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