Finding multiple matches in long rows

[GLIBBY]

Im trying to create an excel file in which I need to find the locations of values that fit criteria in each column of a long (100+ column) row. I also need to repeat this process row by row. I was trying to use a cell based function, since I want the spreadsheet to work as the user changes values, instead of having to re-run a macro.

However, I cant come up with anything that seems to work. It seems like I'll either have to write a macro, or use a solution that would end up with an array function as big as the data itself.

The ideal solution would be if an arbitrary cell (x) could contain the location of the first match, then x+1 could contain the second location, etc... for as many matches as there are. Im not entirely sure this is feasible however.

Any suggestions?

Thanks.

 

[Marcelo Branco]

Hi,

See if this helps

http://www.vbaexpress.com/kb/getarticle.php?kb_id=8

M.

 

[T. Valko]

Im trying to create an excel file in which I need to find the locations of values that fit criteria in each column of a long (100+ column) row. I also need to repeat this process row by row. I was trying to use a cell based function, since I want the spreadsheet to work as the user changes values, instead of having to re-run a macro.

However, I cant come up with anything that seems to work. It seems like I'll either have to write a macro, or use a solution that would end up with an array function as big as the data itself.

The ideal solution would be if an arbitrary cell (x) could contain the location of the first match, then x+1 could contain the second location, etc... for as many matches as there are. Im not entirely sure this is feasible however.

Any suggestions?

Thanks.

Need more specific details.

 

[GLIBBY]

Marcelo, thank you for the link. Its almost what I need but its hampered by the limited ability of the V and Hlookup functions. Heres a (hopefully) better explanation.


I have long rows of data, say:

a b c d e f g h i j k
1 2 3 4 5 6 5 7 8 9
1 2 5 5 3 4 6 7 8 9


For each row I want to find the column number of every instance of a specific value (say 5) in that row. I day dreamed that I could possibly have a duplicate number of cells, the same size as the data and check each cell individually, however that would be a very in-efficient method.

The problem with Hlookup is that there a multiple instances (solved by the above macro) and also the fact that the rows Im searching through are not always going to be the first row in the list.

I was hoping I could find some method to return the column of the Nth match to a specific value. Then I could have a small number off cells to the right of the data, that would display the column of the Nth match, if it existed. Since I dont anticipate a condition where every column of data would be a match, I think this would work.

 

[T. Valko]

Marcelo, thank you for the link. Its almost what I need but its hampered by the limited ability of the V and Hlookup functions. Heres a (hopefully) better explanation.


I have long rows of data, say:

a b c d e f g h i j k
1 2 3 4 5 6 5 7 8 9
1 2 5 5 3 4 6 7 8 9


For each row I want to find the column number of every instance of a specific value (say 5) in that row. I day dreamed that I could possibly have a duplicate number of cells, the same size as the data and check each cell individually, however that would be a very in-efficient method.

The problem with Hlookup is that there a multiple instances (solved by the above macro) and also the fact that the rows Im searching through are not always going to be the first row in the list.

I was hoping I could find some method to return the column of the Nth match to a specific value. Then I could have a small number off cells to the right of the data, that would display the column of the Nth match, if it existed. Since I dont anticipate a condition where every column of data would be a match, I think this would work.

Try this...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	_	_	_	_	_	_	_	_	_	_	Lookup	Count	Columns	_
2	1	2	3	4	5	6	5	7	8	9	5	2	5	7
3	1	2	5	5	3	4	6	7	8	9	_	2	3	4
Sheet1

K2 = lookup value

Enter this formula in L2 and copy down as needed:

=COUNTIF(A2:J2,K$2)

This will return the count of lookup values per row.

Enter this array formula** in M2:

=IF(COLUMNS($M2:M2)>$L2,"",SMALL(IF($A2:$J2=$K$2,COLUMN($A2:$J2)),COLUMNS($M2:M2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This will return the absolute column numbers of cells that contain the lookup value.

Copy down as needed then across until you get a full column of blanks.

 

[GLIBBY]

Thats exactly what I needed, thank you!

 

[T. Valko]

Thats exactly what I needed, thank you!

You're welcome. Thanks for the feedback!