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

[pepe86]

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

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H
1	List 1	Other 1	Other 2	Other 3	List 2 Item?			List 2
2	Item 1	data	data	data				Item 3
3	Item 2	data	data	data				Item 4
4	Item 3	data	data	data	x			Item 8
5	Item 4	data	data	data	x
6	Item 5	data	data	data
7	Item 6	data	data	data
8	Item 7	data	data	data
9	Item 8	data	data	data	x
10	Item 9	data	data	data
11	Item 10	data	data	data
12
Lists

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	List 1	Other 1	Other 2	Other 3				List 1		List 1	Other 1	Other 2	Other 3
2	Item 1	1	11	21				Item 3		Item 3	3	13	23
3	Item 2	2	12	22				Item 4		Item 4	4	14	24
4	Item 3	3	13	23				Item 8		Item 8	8	18	28
5	Item 4	4	14	24
6	Item 5	5	15	25
7	Item 6	6	16	26
8	Item 7	7	17	27
9	Item 8	8	18	28
10	Item 9	9	19	29
11	Item 10	10	20	30
12
Lists

 

[pepe86]

Wow,

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

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	List 1	Other 1	Other 2	Other 3				List 1	List 1		List 1	Other 1	Other 2	Other 3
2	Item 1	1	11	21				Item 1	=Item 1		Item 1	1	11	21
3	Item 2	2	12	22				Item 4	=Item 4		Item 4	4	14	24
4	Item 3	3	13	23				Item 8	=Item 8		Item 8	8	18	28
5	Item 4	4	14	24
6	Item 5	5	15	25
7	Item 6	6	16	26
8	Item 7	7	17	27
9	Item 8	8	18	28
10	Item 9	9	19	29
11	Item 10	10	20	30
12
Lists

 

[pepe86]

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.

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]

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.