Hi,
I have a file with immunophenotypic markers and their relationship to certain blood cells.
What I need to do is search the list and pull out the groups of defining markers for each blood cell. Some of the cells have many markers, some only have one but I need to find which combinations of markers identify each cell if possible.
I have attached the file (I think!)
Thanks,
Colm O'Shea
Need some more description about how to match it and expected result. Without the clear info it is very hard to assume your expected result
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Sorry,
I am hoping to extract groups of the markers which identify each cell.
e.g. for T cells, the combination of CD3, CD7 and BCL-2 can only be T cells and not any other cell that may show each of those markers or a combination of two of them.
For some cells, there would be a number of combinations and for others, there will be no differentiating marker.
Not sure if that helps, but any assistance would be great as I'm struggling to pull them out with lookup, etc.
Colm
Won't be quick but I think it works... I aimed for if there was more than one unique combination then provide the shortest one.
It's a macro but I can't imagine this being very easy to do with functions. Press Alt+F11 to open vba editor, click insert-> module then copy and paste below text and f5 to run. On the example you provide probably looking at around 30 minutes to 1 hour. If you have a bigger "real" dataset it will get exponentially worse the bigger the dataset gets. I don't really see how you can do it other than testing every possible variation of markers.Edit, just saw your post with further explanation... for the T-cells, CD3 on it's own is unique (it only exists in T-cells), so any combination involving CD3 from the available T-cells would also be unique, what do you want as the result here?
Option Explicit Sub macro_1() Dim count_row, m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12, count_markers, found_markers, unique_arrangement For count_row = 2 To Range("A" & Rows.Count).End(xlUp).Row For m1 = 0 To 1 For m2 = 0 To 1 For m3 = 0 To 1 For m4 = 0 To 1 For m5 = 0 To 1 For m6 = 0 To 1 For m7 = 0 To 1 For m8 = 0 To 1 For m9 = 0 To 1 For m10 = 0 To 1 For m11 = 0 To 1 For m12 = 0 To 1 found_markers = False For count_markers = 2 To Range("A" & Rows.Count).End(xlUp).Row If Not count_row = count_markers Then If m1 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("B" & count_row)) > 0 Then If m2 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("C" & count_row)) > 0 Then If m3 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("D" & count_row)) > 0 Then If m4 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("E" & count_row)) > 0 Then If m5 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("F" & count_row)) > 0 Then If m6 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("G" & count_row)) > 0 Then If m7 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("H" & count_row)) > 0 Then If m8 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("I" & count_row)) > 0 Then If m9 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("J" & count_row)) > 0 Then If m10 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("K" & count_row)) > 0 Then If m11 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("L" & count_row)) > 0 Then If m12 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("M" & count_row)) > 0 Then found_markers = True End If End If End If End If End If End If End If End If End If End If End If End If End If Next count_markers If found_markers = False Then unique_arrangement = "" If m1 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("B" & count_row) If m2 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("C" & count_row) If m3 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("D" & count_row) If m4 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("E" & count_row) If m5 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("F" & count_row) If m6 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("G" & count_row) If m7 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("H" & count_row) If m8 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("I" & count_row) If m9 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("J" & count_row) If m10 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("K" & count_row) If m11 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("L" & count_row) If m12 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("M" & count_row) If Len(Range("O" & count_row)) = 0 Or Len(unique_arrangement) < Len(Range("O" & count_row)) Then Range("O" & count_row) = Strings.Right(unique_arrangement, Strings.Len(unique_arrangement) - 2) End If Next m12 Next m11 Next m10 Next m9 Next m8 Next m7 Next m6 Next m5 Next m4 Next m3 Next m2 Next m1 Next End Sub
Last edited by ragulduy; 12-17-2013 at 07:18 AM.
Or take a look at this.
Change the layout a little
Search cel in A1 en use button 'filter on'
Last edited by popipipo; 12-17-2013 at 08:03 AM. Reason: update attachment
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Wow.
Thanks a million!
Colm
Essentially, I'll need to either have a list of each combination for each cell or each of the differentiating markers (say CD3 for T cells) with the common markers associated with it.
The problem is the variety of possible responses. The cell in question could have only one marker that differentiates it, or just one combination that separates it or it may not have any distinguishing markers.
Once I have some form of separation, I can work with that. If it's possible to list each possible combination, that would be most useful.
Thanks for your help. It's really helping me out!
Colm
made a update of the attachment.
Maybe something like this but there will be loads of combinations for some of them...
Option Explicit Sub macro_1() Dim count_row, m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12, count_markers, found_markers, unique_arrangement, count_cols For count_row = 2 To Range("A" & Rows.Count).End(xlUp).Row count_cols = 15 For m1 = 0 To 1 For m2 = 0 To 1 For m3 = 0 To 1 For m4 = 0 To 1 For m5 = 0 To 1 For m6 = 0 To 1 For m7 = 0 To 1 For m8 = 0 To 1 For m9 = 0 To 1 For m10 = 0 To 1 For m11 = 0 To 1 For m12 = 0 To 1 found_markers = False If m1 = 1 And Range("B" & count_row) = "" Then GoTo dont_test If m2 = 1 And Range("C" & count_row) = "" Then GoTo dont_test If m3 = 1 And Range("D" & count_row) = "" Then GoTo dont_test If m4 = 1 And Range("E" & count_row) = "" Then GoTo dont_test If m5 = 1 And Range("F" & count_row) = "" Then GoTo dont_test If m6 = 1 And Range("G" & count_row) = "" Then GoTo dont_test If m7 = 1 And Range("H" & count_row) = "" Then GoTo dont_test If m8 = 1 And Range("I" & count_row) = "" Then GoTo dont_test If m9 = 1 And Range("J" & count_row) = "" Then GoTo dont_test If m10 = 1 And Range("K" & count_row) = "" Then GoTo dont_test If m11 = 1 And Range("L" & count_row) = "" Then GoTo dont_test If m12 = 1 And Range("M" & count_row) = "" Then GoTo dont_test For count_markers = 2 To Range("A" & Rows.Count).End(xlUp).Row If Not count_row = count_markers Then If m1 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("B" & count_row)) > 0 Then If m2 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("C" & count_row)) > 0 Then If m3 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("D" & count_row)) > 0 Then If m4 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("E" & count_row)) > 0 Then If m5 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("F" & count_row)) > 0 Then If m6 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("G" & count_row)) > 0 Then If m7 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("H" & count_row)) > 0 Then If m8 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("I" & count_row)) > 0 Then If m9 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("J" & count_row)) > 0 Then If m10 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("K" & count_row)) > 0 Then If m11 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("L" & count_row)) > 0 Then If m12 = 0 Or WorksheetFunction.CountIf(Range("B" & count_markers & ":N" & count_markers), "=" & Range("M" & count_row)) > 0 Then found_markers = True Exit For End If End If End If End If End If End If End If End If End If End If End If End If End If Next count_markers If found_markers = False Then unique_arrangement = "" If m1 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("B" & count_row) If m2 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("C" & count_row) If m3 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("D" & count_row) If m4 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("E" & count_row) If m5 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("F" & count_row) If m6 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("G" & count_row) If m7 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("H" & count_row) If m8 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("I" & count_row) If m9 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("J" & count_row) If m10 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("K" & count_row) If m11 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("L" & count_row) If m12 = 1 Then unique_arrangement = unique_arrangement & ", " & Range("M" & count_row) Cells(count_row, count_cols) = Strings.Right(unique_arrangement, Strings.Len(unique_arrangement) - 2) count_cols = count_cols + 1 End If dont_test: Next m12 Next m11 Next m10 Next m9 Next m8 Next m7 Next m6 Next m5 Next m4 Next m3 Next m2 Next m1 Next End Sub
Wich solution?Wow.
Both are great as I now have a list of combos and I can filter them manually to have a look at similar groups (which is so handy as I now need to find a way of separating them outside of their markers).
I was talking about yours with the Wow though as I hadn't seen yudlugar's macro at the time.
You fellas are wizards, tbh.
If you are using mine check the 'x' whether they are in the right place!!
I found a mistake somewhere.
Hi again popipipo,
There's something amiss with the table or with the user!
e.g. The B cells should have a number of markers specific only to them (CD19, CD20, PAX-5) but when it filters, it shows nothing.
There are a number of similar cell-marker relationships not showing up.
Am I doing something wrong here?
That is what i saidI found a mistake somewhere.
Try this one
That really is a great help.
Thank you so much.
Colm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks