Want help in excel sheet

[Ashish Namdeo]

Hi all,

I have an excel sheet having two columns. Data is similar to like
below

A B
1 1
2 2
3 4
4 4
5 5
6 5
7 8
8 1
9

What I want, is a third column, say C, in excel, having all the
numbers which are
1) In Column B also In column A
2) Number should not be repeating.

I also want a forth column say D having repeated numbers. So all I
want is

A B C D
1 1 2 1
2 2 8 4
3 4 5
4 4
5 5
6 5
7 8
8 1
9

How this can be done in excel ? I think this can not be done using
standard excel feature and I may need to write some macros. Not sure
how this can be done ? Any help ?
Thanks in advance.

Ashish

 

[Peter Atherton]

Ashish

And with a macro
Sub NumberLists()
Dim SearchVals As Range, RngToSearch As Range ', temp As
Range
Dim c As Variant, b As Variant
Dim Count As Integer, Counta As Integer, i As Integer
Count = 1: Counta = 1
Set RngToSearch = Range("A2:A10") 'change these ranges
Set SearchVals = Range("B2:B9")

On Error Resume Next
'put vals in temp location
For i = 2 To 9 'in rngtosearch
Cells(i, 1).Select
c = ActiveCell.Value
Debug.Print c
For Each b In SearchVals
If c = b Then
Count = Count + 1
Cells(Count, 6).Value = b
Else: Count = Count
End If
Next b
Next i

'Move the temp values into columns C and D
Range("f2").Select
r = ActiveCell.CurrentRegion.Rows.Count
Range("G2").Value = r
temp = Range(Cells(2, 6), Cells(r, 6))
Count = 1: Counta = 1
For i = 2 To r
Cells(i, 6).Select
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
Count = Count + 1
Cells(Count, 4).Value = ActiveCell.Value
ElseIf ActiveCell.Value <> ActiveCell.Offset(1,
0).Value _
And ActiveCell.Value <> ActiveCell.Offset(-1, 0) Then
Counta = Counta + 1
Cells(Counta, 3).Value = ActiveCell.Value
End If
Next i

'Clear the Temp list
Range(Cells(2, 6), Cells(Cells(2, 7).Value,
7)).ClearContents

End Sub

 

[Ashish Namdeo]

Thanks Tony, It works great.. I wasn't aware that excel can do this.