How to select non consecutive colums out of a hugh batch of data?

pepe86

New Member
Joined
Jan 16, 2011
Messages
3
Hi guys,

my problem is the following:

I have a list ("list1") of 20,000 genes and another list ("list2") with a selection of 480 genes. The list containing 480 genes is providing the names only. The actual gene information is in the list with the 20,000 genes.
I don't want to pick every single gene and copy the information into a second sheet with the gene name. I'd rather prefer to tell Excel to use the 480 gene names in the "list2" to select or mark them in the "list1". By that I should be able to copy the marked cells and copy the whole batch into a new sheet.

?!

Is that possible or something comparable??


Thanks a lot in advance!!


Pepe86
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,310
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Something like this? Then you could filter on column E to get the list to copy to the other sheet.

Excel Workbook
ABCDEFGH
1List 1Other 1Other 2Other 3List 2 Item?List 2
2Item 1datadatadata Item 3
3Item 2datadatadataItem 4
4Item 3datadatadataxItem 8
5Item 4datadatadatax
6Item 5datadatadata
7Item 6datadatadata
8Item 7datadatadata
9Item 8datadatadatax
10Item 9datadatadata
11Item 10datadatadata
12
Lists
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,310
Office Version
  1. 365
Platform
  1. Windows
Another way would be to use Advanced Filter. If you give the two list the same heading then you can use Advanced Filter to produce the list directly. Advanced Filter is found under the Data menu.

Here's an example done with Advanced Filter:

Excel Workbook
ABCDEFGHIJKLM
1List 1Other 1Other 2Other 3List 1List 1Other 1Other 2Other 3
2Item 111121Item 3Item 331323
3Item 221222Item 4Item 441424
4Item 331323Item 8Item 881828
5Item 441424
6Item 551525
7Item 661626
8Item 771727
9Item 881828
10Item 991929
11Item 10102030
12
Lists
 

pepe86

New Member
Joined
Jan 16, 2011
Messages
3
Wow,

thanks a lot for this quick and very helpful answer!!
1st Problem solved, but unfortunately it causes another one :LOL: !

The filter filters also names which containing the same character, even though the name is different.

Example: BOSS --> BOSSANOVA

Is there a way to omit those false partners or to tell Excel to pick only 100% matches?!

Thanks in advance!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,310
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are correct. With my previous example, if I had included 'Item 1' in my column H list then my results would have included 'Item 1' and also 'Item 10'. To avoid that and get an exact match the items in the criteria list must be preceded with an = sign.

One way would be to format column H as 'Text' and then insert an = sign in front of each entry. However, given that your list alsready exists and contains nearly 500 items, an easier way would be as follows.

For my previous layout, in column I ..

1. Repeat the heading.

2. Insert the formula shown and copy down.

3. Do the Advanced Filter again but use the column I list as the Criteria Range in the Advanced Filter.

Excel Workbook
ABCDEFGHIJKLMN
1List 1Other 1Other 2Other 3List 1List 1List 1Other 1Other 2Other 3
2Item 111121Item 1=Item 1Item 111121
3Item 221222Item 4=Item 4Item 441424
4Item 331323Item 8=Item 8Item 881828
5Item 441424
6Item 551525
7Item 661626
8Item 771727
9Item 881828
10Item 991929
11Item 10102030
12
Lists
 

pepe86

New Member
Joined
Jan 16, 2011
Messages
3
Thanks again for the answer. That's a very useful formula in generell and it worked fine for my purpose!

Since this forum can answer so many questions I would like to ask another one. :rolleyes:

Let's say my new filtered list with contains only 100% matches is actually shorter than the criteria range list, because not all the stuff in the criteria list is also in the filtered list.
To have a better overview in what is missing and what's there I would like to distribute the criteria list (which is right of the filtered list) among the filtered list. Something like that:

A A
B B
C D
D E
E

to:

A A
B B
C
D D
E E


Thanks in advance!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,310
Office Version
  1. 365
Platform
  1. Windows
I don't fully understand what you have in your sheet and what you are after.

Does your data have multiple columns like in my smaple or is it just a single column list as is indicated by your previous post?

It would be helpful if you could construct a small set of sample of data and expected results and show that, including the required sheet layout. My signature block below contains 3 methods for posting small screen shots. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,193
Messages
5,768,774
Members
425,492
Latest member
blueexcel123

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
Top