0

I am looking for a way to group rows by linking the values of the cells (see link for image). For example, Row 1 is linked to row 2 by the letter "B", and row 2 is linked to row "3" by the letter C -- making those three rows group 1. Whereas, row 4 has no links and is considered group 2.

Example image

UPDATE: When I say 'linked' I mean that rows can be grouped by a common value in a cell. Given my first example, the letter "B" is present in both row 1 and row 2, which I then consider 'linked'. See the next link for a sub-sample of my original, but more complex, data.

Sub-sample of original data

I apologize in advance if this question has been addressed in an earlier post but I simply don't know how to phrase it.

ekad
  • 14,436
  • 26
  • 44
  • 46
Ross
  • 359
  • 2
  • 11
  • Can you explain better when you consider rows "linked". It is always "the value in column B is the value in column A for the next row"? Do you want (sequential) row numbering like you did in your image? – Floris Feb 24 '14 at 05:33
  • Is this like a clustering-problem? Those are normally incremental, so I would say you would need to use VBA for that... – AKDADEVIL Feb 24 '14 at 13:13
  • @akdadevil - I believe you are right - although it may be possible to do this somewhat more efficiently by creating an nxn table with ones at the intersection of existing `A` and `B` values... – Floris Feb 24 '14 at 13:23
  • possible duplicate of [identify groups of linked episodes which chain together](http://stackoverflow.com/questions/12135971/identify-groups-of-linked-episodes-which-chain-together) – thelatemail Aug 27 '15 at 22:43

2 Answers2

0

A simple solution (if I understand your problem) - Assuming you have data in columns A and B, and group numbers in C.

Put the number 1 in the first row, "group" column (cell C1). Then put the following formula in cell C2, and drag down:

=IF(B1 = A2, C1, C1 + 1)

when the cells match, the group number is the same, otherwise it increments. Simple sample:

enter image description here

Formula view of the same thing:

enter image description here

Floris
  • 45,857
  • 6
  • 70
  • 122
  • Thanks very much for your help, Floris. When I say 'linked' I mean that rows can be grouped by a common value in a cell. Given my first example, the letter "B" is present in both row 1 and row 2, which I then consider 'linked'. I tried your formula suggestion, and it works if the columns are sorted in a logical order, but my data are sometimes not in a logical order. Here is a link of a sample of my original, but more complex, data (https://dl.dropboxusercontent.com/u/29079095/Excel_Data.xlsx). I apologise if I'm not making much sense. – Ross Feb 24 '14 at 06:21
  • No it makes sense... But that is a much harder problem. I suggest you update the question with the additional detail. It looks like you are trying to find parts of a linked list that belong together... How big do the numbers get? If its no more than a few 100 I may have some ideas... Otherwise algorithm becomes n^2 ... – Floris Feb 24 '14 at 12:51
0

Managed to find a way of solving this problem using R and the RBGL package. Really appreciate everyone's input. See link...

identify groups of linked episodes which chain together

Community
  • 1
  • 1
Ross
  • 359
  • 2
  • 11