Cell Identification

[mgm05267]

Dear all,

Following is the type of data I have in same column & different rows A1:B11:

A
B
C
D
E
B
G
C
D
E
B

A1 is "A"
A2 is " B"
A4 is " D" & so on...

A contains main numbers like B,C,B
G contains main numbers like C,B
C contains main numbers like D,E

What are all the numbers A,G,C contains remains same.
A is like folder & B,C,B are like files in the folder.

All the names are unique

What I want here is if I enter C in C1 then D should give A

that means C is under A

Can anyone please help me....

Regards,

MGM

[mgm05267]

Hi...

am I asking very complicated here......

[mgm05267]

Please find the attachment

[mikerickson]

Your use of leading spaces (in blocks of 4) to indicate subordination doesn't survive this boards interface. Neither is it clear to the spreadsheet's user. I've substituted - for space and your column A becomes.

A
----B
----C
--------D
--------E
----B
G
----C
--------D
--------E
----B

In the OP you say that the names are unique, get everything except A and G are repeated.

With C input, you say that you want A returned.
I can't see the logic behind that, since ----C is subordinate to both A and G.

Using a different column would be a clearer indication of subordination than leading blocks of four spaces.

[mgm05267]

Hi mikericson,

Thanks for understanding the issue.

This A & G are assemblies & B is a part used in A & G. C is a sub assembly & D & E are parts in C.

In this case, first B will be identified as unique & next B will be identified as repeated in the next column.

By using find option in VBA, the result I get will be First B, no matter even if there are more "B"'s used.

I tried by using in different columns like B,C in column B & D, E in column C, but when I filter B, I can't see A

Its enough for me to get A as result for C as input even if it is there in G because C is unique or first time available in the whole assembly where as C available in G will be identified as repeated one.

Am trying this in VBA form. in textbox1, I put C & in textbox2, I want to get A as result.

Please help...

Regards,

MGM

[mikerickson]

Using your system of leading spaces, something like this should work.
[VBA]Sub test()
Dim DataRange As Range
Dim rngFoundComponent As Range
Dim ComponentName As String
Dim firstFoundAddress As String
Dim rngAssembly As Range, AssemblyName As String
Dim r As Long

Set DataRange = Sheet1.Columns(1)
ComponentName = "C"
Set rngFoundComponent = DataRange.Find(what:=ComponentName, after:=Cells(Rows.Count, 1), LookIn:=xlValues, lookat:=xlPart, searchdirection:=xlNext)
firstFoundAddress = rngFoundComponent.Address
Do
With rngFoundComponent
For r = .Row To 1 Step -1
With .EntireColumn
If LTrim(.Cells(r, 1)) = CStr(.Cells(r, 1)) Then
Set rngAssembly = .Cells(r, 1)
AssemblyName = CStr(rngAssembly)
Exit For
End If
End With
Next r
End With
If 0 < r Then
MsgBox ComponentName & " is a part of assembly " & AssemblyName & vbCr & "In " & rngAssembly.Address
End If
Set rngFoundComponent = DataRange.FindNext(after:=rngFoundComponent)
Loop Until rngFoundComponent.Address = firstFoundAddress
End Sub[/VBA]

[mgm05267]

Hi mikericson,

Thanks for your solution. This works for "C" to give result as "A".

But, if the input is "E", it should give "C" as result, but it gives "A" only....

Please help me....

Regards,

MGM

[mikerickson]

Try this

[VBA]Sub getParent()
Dim DataRange As Range
Dim rngFoundComponent As Range
Dim ComponentName As String
Dim firstFoundAddress As String
Dim rngAssembly As Range, AssemblyName As String
Dim baseLevel As Long
Dim r As Long

Set DataRange = Sheet1.Columns(1)
ComponentName = "D"
Set rngFoundComponent = DataRange.Find(what:=ComponentName, after:=Cells(Rows.Count, 1), LookIn:=xlValues, lookat:=xlPart, searchdirection:=xlNext)
firstFoundAddress = rngFoundComponent.Address

Do
With rngFoundComponent
baseLevel = Level(CStr(.Value))
For r = .Row To 1 Step -1
With .EntireColumn
If Level(CStr(.Cells(r, 1))) < baseLevel Then
Set rngAssembly = .Cells(r, 1)
AssemblyName = LTrim(CStr(rngAssembly))
Exit For
End If
End With
Next r
End With
If 0 < r Then
MsgBox ComponentName & " is a part of assembly " & AssemblyName & vbCr & "In " & rngAssembly.Address
End If
Set rngFoundComponent = DataRange.FindNext(after:=rngFoundComponent)
Loop Until rngFoundComponent.Address = firstFoundAddress
End Sub

Function Level(aString) As Long
Level = Len(aString) - Len(LTrim(aString))
End Function[/VBA]

[mgm05267]

Hi Mickerickson,

You are the MAN......

You read my mind.....

This is what I expected..... Thanks a lot.... a ton....

You made my life simple & breathe easier

Regards,

MGM

[mgm05267]

Hi Mickerickson,

You are the MAN......

You read my mind.....

This is what I expected..... Thanks a lot.... a ton....

You made my life simple & breathe easier

Regards,

MGM