Look for symbols and find a match and copy in a new sheet

[donlincolnmre2]

Hello

I have some symbols in column I

for example

FTR
ASNA
ODP
LYG
GURE

column A also have those symbols, some where in the long list of symbols

example data


AAAP 89173 38.43 N/A 1.71 23.5 5/23/2017
AAC 124771 7.06 N/A 0.14 6.01 5/23/2017
AAL 7026460 46.52 0.4 0.31 24.85 5/23/2017
ODP 11005 81.7 N/A 6.75 12.51 5/23/2017
AAME 2087 3.7 0.02 0 3.06 5/23/2017
AAN 701747 34.5 0.11 -0.31 20.5 5/23/2017
AAOI 2859900 68.8191 N/A -2.4209 8.84 5/23/2017
AAON 161965 36.7 0.26 -0.1 25.33 5/23/2017
LYG 1037480 141.05 0.24 -6.32 134.08 5/23/2017
AAPC 1455 10.6099 N/A 0 7.6 5/15/2017


i'm looking for a macro that will read the symbols in column I and the find them in Column A and then extract that whole row in a new sheet and then clear that cell contents of column I and then go to next symbol in ROW I2 and then do the same, repeat till all the symbols are gone from column I.

Any help would be greatly appreciated,

Thanks.

 

[jolivanes]

Try on a copy of your workbook.
Change references as required.

Sub Maybe_So()
Dim i As Long, c As Range
    For i = 2 To Cells(Rows.Count, 9).End(xlUp).Row
        For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            If Trim(Split(Trim(c), " ")(0)) = Trim(Cells(i, 9)) Then
                c.EntireRow.Copy Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                    Cells(i, 9).ClearContents
                Exit For
            End If
        Next c
    Next i
End Sub

 

[donlincolnmre2]

Hello jolivanes

I tried that macro, it worked fine, but i had the logic error in there. it turns out here is what i was looking for the macro to do, hopefully you can help me with, i would really appreciate it.

Thanks.

Here is the Data

These are colum A through G

AQB 22033 8.03 N/A -0.2 0.01 5/26/2017
AQB 5
ARCH 464609 72.27 1.4 0.8 0.04 5/26/2017
ARCH 8
BAA 883375 0.78 N/A -0.05 0.07 5/26/2017
BAA 4
CVM 3681680 0.0865 0 0.0015 0.06 5/26/2017
DCTH 15774100 0.0247 N/A 0.0006 0.02 5/26/2017
DELTW 6400 0.0508 N/A 0 0.08 5/25/2017
ETRM 827428 5.09 N/A -0.09 0.04 5/26/2017
ETRM 6
HOTRW 103041 0.25 N/A -0.03 0.02 5/26/2017
IDXG 2030390 1.79 N/A -0.08 0.07 5/26/2017
MICTW 7911 0.14 N/A 0.03 0.05 5/26/2017

The macro should find blank cell in COLUMN C

once found then the macro should cut column A, B and C of that Row and move them in the previous row in COLUMN J

Delete that row where the blank cell was found

Do untill no more blank cell in coulmn D

Thanks.

 

[jolivanes]

Are these in Column G?
AQB 5
ARCH 8
BAA 4
ETRM 6

and these in Column A?
AQB 22033 8.03 N/A -0.2 0.01 5/26/2017
ARCH 464609 72.27 1.4 0.8 0.04 5/26/2017
BAA 883375 0.78 N/A -0.05 0.07 5/26/2017
CVM 3681680 0.0865 0 0.0015 0.06 5/26/2017
DCTH 15774100 0.0247 N/A 0.0006 0.02 5/26/2017
DELTW 6400 0.0508 N/A 0 0.08 5/25/2017
ETRM 827428 5.09 N/A -0.09 0.04 5/26/2017
HOTRW 103041 0.25 N/A -0.03 0.02 5/26/2017
IDXG 2030390 1.79 N/A -0.08 0.07 5/26/2017
MICTW 7911 0.14 N/A 0.03 0.05 5/26/2017

What do the numbers (5, 8, 4, 6) in the symbols signify?
You only want to use the letters from these symbols for comparison, right? Not the numericals I assume.
Would there be only one or possible more of these longer strings with the symbol?

The following four sentences need some more in depth explaining for me.
The macro should find blank cell in COLUMN C <----- ????? There could be over a million blank cells for excel 2007 and up
Once found then the macro should cut column A, B and C of that Row and move them in the previous row in COLUMN J <----- Cut Columns? I assume you mean cells in Columns A, B, C.
Delete that row where the blank cell was found <---- If you delete the row, why deleting cells as in the previous sentence?
Do until no more blank cell in column D <------ Where does Column D fit in?

 

[donlincolnmre2]

Hello jolivanes

Caps are just to separate my response.

HERE IS THE SCREEN SHOT OF DATA HOW ITS LAID OUT.

https://1drv.ms/i/s!AskgROwjljL3hn6JZHHtylyp1fee

What do the numbers (5, 8, 4, 6) in the symbols signify?
ITS JUST A DUMMY DATA, ITS DUMMY STOCK VOLUME, COLUMN A CONTAINS STOCK PRICES AND B CONTAINS VOLUME.

You only want to use the letters from these symbols for comparison, right? Not the numericals I assume.
Would there be only one or possible more of these longer strings with the symbol?
I HAVE ANOTHER FILE WITH SOME STOCK SYMBOLS AND PRICES, THE MASTER FILE CONTAINS OVER 6000 LINES OF DATA, WHAT I'M TRYING TO ACCOMPLISH IS PUT MY DATA AT THE TOP OF THE FILE WHICH HAS SYMBOLS IN COLUMN A AND STOCK PRICE IN COLUMN B, WHEN I SORT THAT FILE THE COMPUTER WILL ARRANGE MY SYMBOLS AND BRING IT CLOSER TO WHERE THE OTHER SYMBOLS ARE FROM THE MASTER FILE, ONCE THAT ACCOMPLISHED THEN I WANT TO MOVE MY SYMBOLS AND THE PRICE DATA RIGHT NEXT TO DATA THAT CAME FROM THE MASTER FILE IN COLUMN J, OF THE MASTER FILE DATA, ONCE THE DATA IS ARRANGED LIKE THAT, THEN I CAN DO CERTAIN CALCULATIONS ON IT.

The following four sentences need some more in depth explaining for me.
The macro should find blank cell in COLUMN C <----- ????? There could be over a million blank cells for excel 2007 and up
AS YOU CAN SEE FROM THE ABOVE LINK THAT I SENT SOME OF THE COLUMN C HAS BLANK DATA.
I THINK EXCEL HAS THAT FUNCTION IF NOT THEN THE CHECK CAN BE PERFORMED ON COLUMN C AND B, IF COLUMN C IS BLANK AND COLUMN B HAS DATA THEN CONTINUE MACRO.

Once found then the macro should cut column A, B and C of that Row and move them in the previous row in COLUMN J <-
---- Cut Columns? I assume you mean cells in Columns A, B, C.
RIGHT CELL IN COLUMNS A,B,C OF THAT ROW. AND MOVE THEM ABOVE COLUMN J OF THAT ROW.

Delete that row where the blank cell was found <---- If you delete the row, why deleting cells as in the previous sentence?
IF YOU DELETE THAT ROW THEN THE COMPUTER WILL NOT FIND A BLANK CELL IN COLUMN C. OF THAT ROW WHERE THE DATA WAS PICKED UP FROM, SO ONCE ALL THE BLANK ROWS ARE GONE IN COLUMN C IT WILL STOP.

Do until no more blank cell in column D <------ Where does Column D fit in?[/QUOTE]
I MEANT TO SAY COLUMN C

Thanks.

 

[jolivanes]

I am still lost.
Maybe use DropBox for a file to look at.
It should have a before and after in it and an explanation how you derived at the "after".
Unless someone else understands what is needed.

 

[donlincolnmre2]

I am still lost.
Maybe use DropBox for a file to look at.
It should have a before and after in it and an explanation how you derived at the "after".
Unless someone else understands what is needed.

HELLO

HERE IS THE LINK TO EXCEL FILE.

https://1drv.ms/x/s!AskgROwjljL3hn_37yaWhuo5HNMf

Hope this helps

Thanks.

 

[jolivanes]

Try this on a copy of your workbook as per your example.
Run code while Sheet1 is active.

Sub Maybe_So()
Application.ScreenUpdating = False
Dim c As Range

    For Each c In Sheets("Sheet1").Range("A3", Sheets("Sheet1").Range("A3").End(xlDown))
        Sheets("Sheet1").Range("A:A").Find(What:=c.Value, After:=Range("A9"), LookIn:=xlFormulas, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Offset(, 9).Resize(, 2).Value = c.Resize(, 2).Value
    Next c
    
With Sheets("Sheet1")
    .Range("A3", .Range("A3").End(xlDown)).Resize(, 2).ClearContents
        .UsedRange.Sort Key1:=Range("A1"), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
Application.ScreenUpdating = True
End Sub

 

[donlincolnmre2]

Try this on a copy of your workbook as per your example.


Hello jolivanes

The macro worked great, thanks for all your help.