Look for something in sheet1 if it meets a criteria paste entire row in sheet2 without gap

[perthhelp]

Hello guys,

I am not an excel guru like you helpful people, so thank you in advance.

I have a heap of data that i am trying to break up in to different worksheets. If worksheet1 column E contains the letters AB - but not the letters AB4 - (I have AB1, AB2, AB3, AB2/AB3, AB1/AB2, AB4, CA1, CA2, etc) how do i then copy and paste the entire row into sheet 2 without any blank rows as i go down?

Thanks Again!

[bob33]

It would help if you sent an example file.

[63falcondude]

Hello and welcome to the forum.

The easiest way to do this would probably be by using a filter.

Highlight column E > Data > Filter > Click on the filter in cell E1 > Text Filters > Custom Filter
contains: AB
And
does not contain: AB4
OK

Now highlight all of your data and copy it then paste into Sheet2.

[perthhelp]

Thanks for that. However i wont be able to do that, because i will need people to be able to use the file without having my knowledge.

[63falcondude]

I am afraid that I do not understand the issue.

how do i then copy and paste the entire row into sheet 2 without any blank rows as i go down?

I answered this question in post #3.

Thanks for that. However i wont be able to do that, because i will need people to be able to use the file without having my knowledge.

Are you saying that other people are going to be doing this but don't know how to filter and copy/paste? I would say to use your knowledge to show them.

If this is not what you were picturing, what does an ideal solution look like to you?

[perthhelp]

my ideal solution would be, when i get paste the data in sheet1, all the different sheets i.e. sheets2,3,4,etc populate automatically based on sheet1. I hope that makes sense?

What i am saying is that, the knowledge i have regarding the data, they wont necessarily have. I wont and they wont always be there to keep sharing the knowledge with the others who will use it.

I will try uploading a test workbook i just made.

Thanks for your help!

[63falcondude]

If you want this to be automated, that is not copy/paste. I would use Power Query for this.

1) Highlight A1:G11 > Data > From Table > OK
2) Select the drop-down in the Place header > Text Filters > Contains > contains: AB > And > does not contain: AB4 > OK
3) Close & Load

Now, as new data is added to the bottom of the table in Sheet1, all that the user has to do is refresh the table in Sheet2.
If you want to refresh all of the tables at once, that can be done through Data > Refresh All.

See attachment.

[perthhelp]

oh thats neat.

And if i dont have power query?

[63falcondude]

Excel 2016 and newer comes with Power Query (Get & Transform).

If you have Excel 2010 or 2013, you can download the add-in for free directly from the Microsoft site.

[perthhelp]

I dont have permission to download. We use the basic excel 2010. Not sure if i will be able to download it.

[63falcondude]

I get the feeling that anything I recommend isn't going to work for you. I'll send out a request for someone else to assist if they choose to.

[perthhelp]

we are stuck in a world of formulas and vba codes.
i wrote a formula but i dont know how to skip the blanks. I also wasnt able to exclude AB4.
thanks for your help, i will try to see if IT will download for me.

[63falcondude]

If you are okay with formulas, you can change your data into a table then use this in A2 of Sheet2:

=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(ISNUMBER(SEARCH("AB",Table1[Place]))*ISERROR(SEARCH("AB4",Table1[Place])),ROW(Table1[Place])),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula to the right and down as far as needed then format column A as dates.

See attachment.

[perthhelp]

Hey bud, i couldnt get the table1[place] to work. it would error out even when i made sheet1 as a table.

[AliGW]

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.