Checking existing data for pairs and looping

[cheeseslice]

Hi,

I am hoping someone can give me a hand.

I have a table of data, 100 rows and 6 columns wide. Each row of data will look something like this:

ch ph bi fr gh it

what i need to do is loop through each row and check for each pair e.g. ch+ph, ch+bi, ch+fr ... ph+bi

I then have to how many of these pairs exist all together. so I have made another table which needs to be updated with this data.

I have never really had to use vba before so I don't know what commands I can use to achieve this.

I don't know if there are built in functions that would make this task quite simple or if I am going to have to stimpulate each pair etc. something like this

IF EXISTS ch AND ph IN ROW THEN ADD 1 TO CELL A1
IF EXISTS ch AND bi IN ROW THEN ADD 1 TO CELL A2

any help would be much appreciated.

[stevebriz]

Can you attach asample workbook?

[cheeseslice]

please find the workbook attached.

[stevebriz]

In your matrix...there cominations for eg: Bi -Hi and Hi -Bi?
Heres my question.
No sure if the sequence of the combination matters to you or do you want to sum these and populate the upper right corner of the matrix?

[cheeseslice]

I'll try and explain what I'm doing.

Each column represents a time slot, the 2 letters are obviously just a subject code. I need to determine how many students study each combination.

So to answer your question it doesn't matter if it's hi bi or bi hi, I just need to know the total number of students that are studying this combination.

I hope that makes a bit more sense.

should I be using string functions?

I've spent today giving myself a crash course in vba.

thanks for replying.

[stevebriz]

I have attached your workbook with some macros in it that shoud do want you want.
If you have any questions...let me know
Steve

[cheeseslice]

thanks so much for this, i wasn't expecting you to do it.

i was about to ask you if it was possible to convert the entire table into one long string and work from there.

i'll have to look over your code now and work out how you have done this.

thanks again