Copy and Paste vs Formula use

[Samantha S]

Hello,

I am working with a large amount of data that I need to narrow down to specific information. We have data that adds a note, either based on user input or system automation. I am looking to narrow down to notes that end with COMP (signifying completion) or CANCEL (signifying the ticket was canceled by the requestor). I can do a simple filter function but when I highlight my information and select Copy, I get an error from Excel.

I've tried sorting the data by note type and note text but no dice.. Is there a formula that can be used to extricate the data that I need so I can put it onto another tab? I apologize if this is an infantile question BUT I've been fighting with excel for much longer than I care to admit.

Feeling defeated

 

[sgmpatnaik]

@Samantha

Welcome to chandoo.org,

it is better to upload a sample file with your desire result

Thanks

 

[Samantha S]

Thank you Patnaik!

Attached is a small sample of what I am dealing with. The first tab is all of the notes that kick out (a small sample as I have over 100,000 lines of data) and the second tab is what I expect for the end result.

 

[sgmpatnaik]

@Samantha

can you give me some idea, actually what do you want to do and which column base the data will transfer to next tab. I am little bit confuse on that, as per my understand in your first tab (Starting point - ALL Notes (SS) of the Column C has some note_text as cancel and comp these two words letter to be transferred to next Tab is it correct

 

[Samantha S]

Yes, essentially I am trying to narrow down the lines where the Note_Txt (Column C) ends with either COMP or CANCEL as these signify completed tickets. I am trying to copy that data onto it's own spreadsheet. With a normal sample (~6000 records) I am able to copy/paste withtout issue. But because I am dealing with such a large volume, Excel is getting confused. Once I have the completed records on their own tab, I am able to perform the necessary vLookup to get my completed date.

 

[Luke M]

Not a formula solution, but fastest way to filter & copy your data...
Select your Results Sheet. In K1:K3, put this info exactly as written:
NOTE_TXT
*COMP
*CANCEL

Now, select cell A1, and go to Data - Sort & Filter - Advanced Filter.
Check option for "Copy to another location"
For "List Range", select your entire table of data on Starting Page worksheet
For "Criteria Range", select cells K1:K3 that we filled in at beginning.
For "Copy to", select A:G of Result sheet.
Hit Ok, and you're done.
http://www.contextures.com/xladvfilter01.html

 

[SirJB7]

Hi, Samantha S!

I'd go for Luke M's suggestion, but just in case you want a formula only solution give a look at the uploaded file. It uses 2 helper columns in source worksheet and only one formula in target worksheet:
Source:
H2: =SI(O(DERECHA(C2;4)="COMP";DERECHA(C2;6)="CANCEL");1;0) -----> in english: =IF(OR(RIGHT(C2,4)="COMP",RIGHT(C2,6)="CANCEL"),1,0)
I2: =SUMA(H$2:H2) -----> in english: =SUM(H$2:H2)
Target:
A2: =SI.ERROR(INDICE('Starting point - ALL Notes (SS)'!A:A;COINCIDIR(FILA()-1;'Starting point - ALL Notes (SS)'!$I:$I;0));"") -----> in english: =IFERROR(INDEX('Starting point - ALL Notes (SS)'!A:A,MATCH(ROW()-1,'Starting point - ALL Notes (SS)'!$I:$I,0)),"")

Column A in target is yellow shaded to point out the rows with formulas.

Just advise if any issue.

Regards!

 

[sgmpatnaik]

Samantha

Try This Code, which is the Luke

Sub FilterData()
    Sheets("Ending point - expected result").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    'Selectnion.Clear
    Selection.Clear
    
    Sheets("Starting point - ALL Notes (SS)").Range("A:G").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("Starting Point - ALL Notes (SS)").Range("K1:K3"), CopyToRange:=Sheets("Ending point - expected result").Range("A1"), Unique:=True
    Columns.AutoFit
    Range("A1").Select
    Sheets("Starting point - ALL Notes (SS)").Select
    Range("A1").Select
End Sub

Hope it clear

 

[Samantha S]

Thank you all very much for your assistance! I was able to extract the needed data!

 

[sgmpatnaik]

@Samantha

Glad your problem solve, welcome back if any problem

Thanks