 Search:

# Solution Required

Asked By: Koila    Date: Jan 04    Category: MS Office    Views: 1140

Could you help in finding the solution for:
My problem is:
I have 300X100 matrix filled (in Excel Sheet) with Texts and I have find how
many times first 5 columns in 300 rows are repeated. E.g
A B C D E F G H
A B C D E I J K
M N O P Q R T Y
Here A, B, C,D, E cells are repeated 2 times/in 2 rows. Is there any way in
excel, we can do it or can you write a macro that will do it.

Share:

Sort the rows on the first five columns

In the column after the last column used in row 2 if that is the first row of
the matrix  put the following (I am assuming CW2) :

=IF((A2=A1)*(B2=B1)*(C2=C1)*(D2=D1)*(E2=E1),CW1+1,1)

Now copy that down the 300 rows

The count will increment until one of the first five cells changes.

Question:
Can (based on your example of a 300x100 'grid') the search text also be in the
same row AFTER the original text? or will it only appear once per row?
Example:
row1: ABCDELXZQABCDESN.....ABCDEOSZW

the 'ABCDE' search will find  three occurances in the first data row.

I have to find  out how many times this combination of cells appears in matrix.
It could be one of Fity.

Didn't find what you were looking for? Find more on Solution Required Or get search suggestion and latest updates.