Dependent Values in COLUMN 2 for COLUMN 1

[harsha123]

Hello Guys,


I have two columns, where some values in column 2 would have values that are present in COLUMN 1. So I would like to list out dependent values for coulumn. Is there any way or any formula that would help me in achieving this?

I have about 2.5 K records, so manual process is bit difficult. Also to mention COLUMN 2 might have blank values too, but it would contain values present in COLUMN1.

Below is a sample of what is expected.

Validation 11 Nov.jpg


Regards,
Harsha

[pjwhitfield]

Im sorry, Im not able to understand exactly what your requirements are? You say that column 2 will have values present in column 1 yet none of your examples match that. Its not therefore possible to decide what logic would be applied to column 2

[harsha123]

Sorry I should have meant the column headers instead. Please could you refer the column B&C, where column 1 and Column 2 are marked up.

[AliGW]

I'm sorry - I can't understand the relationship between the columns, either. Could you explain clearly what it is?

[pjwhitfield]

Sorry I should have meant the column headers instead. Please could you refer the column B&C, where column 1 and Column 2 are marked up.

I did, they still dont have any relevance to each other.

[harsha123]

Take for example first row, where column 1 value is 1. So if we would check for value 1 in column2, it is present for values 4,5 , which needs to be marked up.

[harsha123]

Take for example 1st row, where column 1 value is 1. So if we would check for value 1 in column2, it is present for values 4,5 , which needs to be marked up.

[AliGW]

So you want a list of row numbers returning in column A?

[pjwhitfield]

Well that wasnt confusing at all!

I think Ive worked out your requirement...

Are you saying that you take the value in column B and search column C for that value, if its found then the row number is shown in column A? ie B3 contains "3" so you search column C for the number 3 and show the rows its found in column A ie column A becomes "8,9,10"

[harsha123]

Yes. Thats is what I am looking for
apologies for confusing.

[pjwhitfield]

Create the following UDF and then reference it in column A

Please Login or Register  to view this content.

ie in A1 use:

Please Login or Register  to view this content.

[harsha123]

Awesome Bro!!!!!! It worked like a charm.

[harsha123]

it appears to work fine except for the last one in col B(value 14, it should be 1, but it is showing up as NO VALUE),

Capture.PNG

[harsha123]

Hi Guys,

Any idea why the code isnt working for the last row?

Regards,
Harsha

[AliGW]

I have no idea about VBA, but could it be this?

Please Login or Register  to view this content.

What happens if you change the 2 to a 1?

[pjwhitfield]

I have no idea about VBA, but could it be this?

Please Login or Register  to view this content.

What happens if you change the 2 to a 1?

Yep, that would be the problem, the original image showed headers but it appears the file doesnt have them. As Ali says, change the 2 to a 1

[harsha123]

Hi I just checked with my real case scenario, it is giving me the value of the row number, instead it should give me the value in that row number

[pjwhitfield]

Hi I just checked with my real case scenario, it is giving me the value of the row number, instead it should give me the value in that row number

This is why its much easier to work with a sample workbook rather than a screenshot.

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[harsha123]

Function Find_Dependent(invalue)
Dim xRow, LastRow As Integer
Dim outval As String
Dim sht As Worksheet
Set sht = ActiveSheet
LastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
For xRow = 2 To LastRow
If (Cells(xRow, 3).Value = invalue) Then
If (Len(outval) > 0) Then
outval = outval & "," & Cells(xRow, 2).Value
Else
outval = Cells(xRow, 2).Value
End If
End If
Next xRow
Find_Dependent = outval
End Function


With the above code except for the first instance, it is working, for the first value I still get the row number.

[pjwhitfield]

what do you mean by the first value? is it the first in the list
ie the 4 in 4,7,10 or the 2 in 2,3,4