Fill missing character from the series

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

Data located in columns C:J in cells C6:J36</SPAN></SPAN>
In the columns L:S I have filled some numbers and in the same rows columns C:J characters are highlighted according in order of the columns (L:C, M:D, N:E, O:F, P:G, Q:H, R:I, And S:J)</SPAN></SPAN>

Series of the character is used 1, X and 2, what I need to find missing character form 1, X or 2 series</SPAN></SPAN>

For example... </SPAN></SPAN>
In the column L, </SPAN></SPAN>
I have filled number 9 in cell L30, in the same row in the column C "X" is highlighted,</SPAN></SPAN>
I have filled number 4 in cell L33, in the same row in the column C "1" is highlighted,</SPAN></SPAN>
I want to check in the column C, below the "1" till last data row 36 which character is missing out of 1, X and 2</SPAN></SPAN>
If C33=1</SPAN>, C34=2</SPAN></SPAN>, C=35=1</SPAN></SPAN>, C36=1</SPAN></SPAN>, so far missing character is "X" </SPAN></SPAN>

I need Formula Or VBA To fill missing character "X" In cell C40,</SPAN></SPAN>

The same rules will be applied for rest of the columns for finding the missing character all missing results are shown in the row 40 in columns C:J </SPAN></SPAN>

Result data example</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3
4
5			P1	P2	P3	P4	P5	P6	P7	P8		P1	P2	P3	P4	P5	P6	P7	P8
6			1	2	X	X	X	1	X	X
7			1	1	1	1	X	X	1	1
8			2	2	2	1	1	1	1	1
9			X	2	1	2	X	1	2	1
10			2	1	2	2	X	2	1	2
11			1	X	1	2	2	1	2	X
12			1	1	X	2	1	1	1	1									3
13			1	1	X	1	X	2	1	1
14			2	1	X	1	1	X	2	X
15			2	2	2	1	1	X	2	X
16			X	1	2	1	1	1	1	1
17			X	X	X	2	2	2	2	1
18			X	X	2	X	1	2	2	X
19			1	1	1	1	X	1	2	1
20			X	1	2	1	2	X	1	1							4
21			X	X	1	1	1	1	1	X
22			2	X	2	X	1	X	X	X
23			2	2	X	1	1	X	1	X				5
24			2	1	X	2	2	2	1	X					7		5
25			2	1	2	1	1	2	X	1
26			2	X	2	1	X	1	X	1
27			1	X	X	1	2	2	2	1								6
28			2	X	1	1	1	1	1	1				6		3
29			2	1	1	1	X	1	2	X
30			X	2	1	1	1	1	1	1		9
31			2	1	1	1	X	2	2	X
32			X	1	1	2	1	1	1	X
33			1	2	1	1	X	1	1	1		4
34			2	X	X	1	1	1	1	X			5
35			1	1	1	1	1	1	X	X								9
36			1	2	1	1	2	1	1	X			3			9
37
38
39
40			X	1X	2	1X	1X	X	2	2
41
42
43
44
Sheet1

Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Marcelo Branco]

Not sure i understand what you're looking for.
Question
Shouldn't the result in F40 be X instead of 1X?

M.

 

[Kishan]

Not sure i understand what you're looking for.
Question
Shouldn't the result in F40 be X instead of 1X?

M.

Hi Marcelo Branco, True the result in F40 should be "X" not the 1X?, sorry it is my error, you are correct.</SPAN></SPAN>

Thank you for observing it</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Marcelo Branco]

Maybe...

Formula in C40 copied across
=IF(ISNA(MATCH(1,INDEX(C$6:C$36,MATCH(9.99E+307,L$6:L$36)):C$36,0)),1,"")&IF(ISNA(MATCH("X",INDEX(C$6:C$36,MATCH(9.99E+307,L$6:L$36)):C$36,0)),"X","")&IF(ISNA(MATCH(2,INDEX(C$6:C$36,MATCH(9.99E+307,L$6:L$36)):C$36,0)),2,"")

M.

 

[Kishan]

Maybe...

Formula in C40 copied across
=IF(ISNA(MATCH(1,INDEX(C$6:C$36,MATCH(9.99E+307,L$6:L$36)):C$36,0)),1,"")&IF(ISNA(MATCH("X",INDEX(C$6:C$36,MATCH(9.99E+307,L$6:L$36)):C$36,0)),"X","")&IF(ISNA(MATCH(2,INDEX(C$6:C$36,MATCH(9.99E+307,L$6:L$36)):C$36,0)),2,"")

M.

Thank you Marcelo Branco, for giving a formula solution, it is resulting as request.</SPAN></SPAN>

I forgot to express I keep results lines adding below the row 36, could it be flexible instead row 36 get fix in the formula. </SPAN></SPAN>

if I fill series of numbers in the column C till data are entered than could be possible take last row reference from the C in to the formula. it is just my thought</SPAN></SPAN>


Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Marcelo Branco]

With a formula solution you have to adjust the range. Maybe someone can provide a macro.

M.

 

[Kishan]

With a formula solution you have to adjust the range. Maybe someone can provide a macro.

M.

Hi Marcelo Branco, thank you very much for the response. Till anyone gives the VBA solution, i could solve the situation with your formula, which is perfect for now. I changed the formula as below and applied the Formula in cell C4 and copied across.</SPAN></SPAN>

[COLOR=#000000]Formula in cell C4 and copied across
[/COLOR]
=IF(ISNA(MATCH(1,INDEX(C6:C5006,MATCH(9.99E+307,L6:L5006)):C5006,0)),1,"")&IF(ISNA(MATCH("X",INDEX(C6:C5006,MATCH(9.99E+307,L6:L5006)):C5006,0)),"X","")&IF(ISNA(MATCH(2,INDEX(C6:C5006,MATCH(9.99E+307,L6:L5006)):C5006,0)),2,"")

I appreciate your help very much. </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Marcelo Branco]

Maybe this macro.
Results placed two rows below the last row with data

Sub aTest()
    Dim LastRow As Long, LR As Long
    Dim rData As Range, i As Long, rCol As Range
    Dim rSearch As Range, strResult As String
    Dim v As Variant, rFound As Range
        
    LastRow = Cells(Rows.Count, "C").End(xlUp).Row
    Set rData = Range("C6:J" & LastRow)
    
    For Each rCol In rData.Columns
        With rCol.Offset(, 9)
            LR = .Cells.Find(What:="*", after:=.Cells(1), LookIn:=xlValues, _
            Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
            MatchCase:=False, SearchFormat:=False).Row
        End With
        Set rSearch = Range(Cells(LR, rCol.Column), Cells(LastRow, rCol.Column))
        strResult = ""
        For Each v In Array(1, "X", 2)
            Set rFound = rSearch.Find(v)
            If rFound Is Nothing Then strResult = strResult & v
        Next v
       'Result placed 2 rows below the last row with data
        Cells(LastRow + 2, rCol.Column).Value = strResult
    Next rCol
End Sub

M.

 

[Kishan]

Maybe this macro.
Results placed two rows below the last row with data

Sub aTest()
    Dim LastRow As Long, LR As Long
    Dim rData As Range, i As Long, rCol As Range
    Dim rSearch As Range, strResult As String
    Dim v As Variant, rFound As Range
        
    LastRow = Cells(Rows.Count, "C").End(xlUp).Row
    Set rData = Range("C6:J" & LastRow)
    
    For Each rCol In rData.Columns
        With rCol.Offset(, 9)
            LR = .Cells.Find(What:="*", after:=.Cells(1), LookIn:=xlValues, _
            Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
            MatchCase:=False, SearchFormat:=False).Row
        End With
        Set rSearch = Range(Cells(LR, rCol.Column), Cells(LastRow, rCol.Column))
        strResult = ""
        For Each v In Array(1, "X", 2)
            Set rFound = rSearch.Find(v)
            If rFound Is Nothing Then strResult = strResult & v
        Next v
       'Result placed 2 rows below the last row with data
        Cells(LastRow + 2, rCol.Column).Value = strResult
    Next rCol
End Sub

M.

Hi Marcelo Branco, this is an Amazing!! Solved 100% spot on solution.</SPAN></SPAN>

I appreciate your kind help for giving your precious time and for prompt replay</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Marcelo Branco]

You are welcome. Glad to help

M.