Filter some lines from one sheet to another.

[eSimon]

Hello,

I have some data imported from a text file.

Some not important text
Some more not important text
CustomerReferenceNumber 5 Customer_A
CustomerReferenceNumber 7 Customer_B
CustomerReferenceNumber 8 Customer_C
CustomerReferenceNumber 9 Customer_G
CustomerReferenceNumber 12 Customer_K
and more text
and more text
and more text


I would like to have only the lines with "CustomerReferenceNumber" to be filtered to new sheet.
I prefer doing that using formula.
Starting row and ending row can vary from a file to another.
Sample attached.


Thanks

[Jul Stev]

Hi,

You can try to use advance filter for this, after you filter it just copy paste to the new sheet.
Try see attached file

Customerlistupdate.xlsx

[eSimon]

Thanks,

I would like to do that using formula.
I need to prepare a Excel file with an empty sheet where the raw data will be pasted in and the filter will be automatically applied to another sheet. Using advanced filter will require user doing more actions.

Any thoughts?

[L-Drr]

To remove the irrelevant data, use this:
=IF(ISERROR(FIND("CustomerReferenceNumber";$A1))=TRUE;"";A1)
(where your data starts in column A)
drag to the right and below.

Then the next step would be to remove the empty rows somehow

[eSimon]

Great Thanks,

I modified it a bit to have the data on a new sheet.
When the IF is FALSE it leaves empty rows in the begining.
Can i delete rows with no data? Do i have to use a macro for that?

Regards

[L-Drr]

I don't know if it's possible with a formula, but you could also just sort the data

[Marcol]

Try this array formula, confirm with Ctrl+Shift+Enter not just Enter before dragging Across and Down.
In A2

Please Login or Register  to view this content.