Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Solution Required

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

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: 

 

3 Answers Found

 
Answer #1    Answered By: Yvette Griffin     Answered On: Jan 04

Please use meaningful subject lines.

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.

 
Answer #2    Answered By: Brent Brown     Answered On: Jan 04

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.

 
Answer #3    Answered By: Cambria Lopez     Answered On: Jan 04

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.




Tagged: