ID multiple column headings

[Darin Kramer]

Hi there,

Columns A to F have unique headings

Area A2 to F20 will either have a N (blank entry) or a 6 or a 7 in the
cell.

In column G, for rows 2 to 20 I need to identify which column the row
has a 6 or a 7 in. (ie not an N, and the difference between 6 and 7 is
not of importance. What is important is that it is not a N)

Thus for eg
Column headings
A1 is Dogs, and B1 is Cats, and C1 is Mice etc

Data starts
a2 = N, B2 = N, C2 = N etc up to F, then in G2 answer = nil
a3 = 6, B3 = N ,C3 = N etc up to F, then in G3 answer = Dogs
a3 = 6, B3 = 7 ,C3 = N etc up to F, then in G3 answer = Dogs and Cats
a3 = 6, B3 = 6 ,C3 = N etc up to F, then in G3 answer = Dogs and Cats
(still)

Thanks!!!

D

 

[Guest]

Hi Darin

Try the following Macro:

Sub SearchN()
Answer = ""
For i = 2 To 20 ' row 2 to 20
Answer = ""
For j = 1 To 6 ' column A to F
If Cells(i, j).Value <> "N" And Cells(i, j).Value <> "" Then
If Answer = "" Then
Answer = Answer & Cells(1, j).Value
Else
Answer = Answer & " and " & Cells(1, j).Value
End If
End If
Next j
If Answer = "" Then Answer = "nil"
Cells(i, 7).Value = Answer
Next i
End Sub

The result looks like that:
A B C D E F G
Dogs Cats etc
N N N N N N nil
6 N N N N N Dogs
6 7 N N N N Dogs and Cats
6 N 7 N N N Dogs and Mice

Regards
reklamo