I am somewhat new the amazingness that is excel formulas, I know some of the basics but when it comes to more complicated formulas I’m at a loss. I was wondering if someone could point me the right direction for what function I would need or what formula would work for this particular problem. I’m not even sure if this is possible.
I have 4 columns of data, I want something that can look at a row, see what numbers are in that rows columns, then find any other rows that match that columns numbers and group them together. Then put a value to each separate grouping. Then here where it gets difficult, while searching if a column has one of the original number the formula was searching for, but then also finds a different value in another column of the row that has one of the numbers the formula was searching for, it would then also go find that new number and assign it to the same grouping as the first and second row.
I am horrible at explaining things so I have included an example of what I want done.
if row A has 2 in column 1, 403 in column 2, and 270 in column 3 I want any row with any of those numbers in the same columns to be the same number as row A is assigned.
so, if row B only had 403 in column 2, i would want it to have the same number as row A
if row C had 403 in column 2 and 275 in column 3, i would want it to have the same number as row A
if row D had 275 in column 3, i would want it to have the same number as row A
But if Row E had a 403 in column 1 I don’t want it the same number as row A
In the end row A, B, C, and D be in group one with value 1 assigned to it and row E would be in group two and have value of 2 assigned to it.
I have attached an example of the kind of data im working with
I have 4 columns of data, I want something that can look at a row, see what numbers are in that rows columns, then find any other rows that match that columns numbers and group them together. Then put a value to each separate grouping. Then here where it gets difficult, while searching if a column has one of the original number the formula was searching for, but then also finds a different value in another column of the row that has one of the numbers the formula was searching for, it would then also go find that new number and assign it to the same grouping as the first and second row.
I am horrible at explaining things so I have included an example of what I want done.
if row A has 2 in column 1, 403 in column 2, and 270 in column 3 I want any row with any of those numbers in the same columns to be the same number as row A is assigned.
so, if row B only had 403 in column 2, i would want it to have the same number as row A
if row C had 403 in column 2 and 275 in column 3, i would want it to have the same number as row A
if row D had 275 in column 3, i would want it to have the same number as row A
But if Row E had a 403 in column 1 I don’t want it the same number as row A
In the end row A, B, C, and D be in group one with value 1 assigned to it and row E would be in group two and have value of 2 assigned to it.
I have attached an example of the kind of data im working with