• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to copy values based on existing values from the range

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

Please see attached file for more details.
Kindly note, I have edited column names, references, values for sample purpose.

Range A1 to C33 is input.
Range E1 to G66 is output.

Highlighted cells in yellow is output.
In the main file, there will not be two different ranges.
I need to work on the original range only that is range A1 to C33.
I tried doing this using For Each Loop but it is becoming very lengthy and complicated.

Basis idea here is, find each text from column A.
If comment for that text is blank then find immediate next comment for that value.
So, cell A2 = D4E6, cell B2 is blank.
However cell A20 = D4E6 and comment is yhn
So my result for cell B2 to B4 is yhn.
Cell A29 = D4E6, B29 is blanck.
So I copied result from cell B23.

Can anyone please help me in this.
 
Hi @vletm sir, thanks for the help.
I am trying to write condition for the scenario at cell A2. Rest am all done with.
Will try to post my code here. However I have written a loop for this and trying to find more efficient way to do it.
Have a nice day ahead. :)
 
ThrottleWorks
I tried to to ask how ...?
Is it true that You don't know - how have You gotten Your expected Outputs?
If You don't know Your results and You won't comment my shown Outputs ... that's a huge challenge.
 
Hi @vletm sorry for late reply. Both the systems are at different locations. Hence delay in reply.
It is working with three loops, one goes in serial order, one goes in reverse order and last is for blank records.

Will try to post the code.
Take care, stay safe. :)
 
Hi @vletm sir, sorry missed your questions yesterday. I missed the comments in your screenshot. My mistake.
I thought you are asking, how am getting result.

A9 and A10 do not have any comments. However A8 has comment.
Value is B2C3 for A8:A10. That is why A9 and A10 will have comment from A8.

Column A is input column. There might be comments for some values, for some values it will be blank.
We need to pick immediate next comment for the blank cell based on value in cell A column.

B1 = 1a
B2 = 1b
B3 = blank

A1:A3 = ABC
So macro will populate 1b for B3
 
ThrottleWorks
You still missed to answer to my question.
Is it true that You don't know - how have You gotten Your expected Outputs?
If You don't know Your results and You won't comment my shown Outputs ... that's a huge challenge.
... seem You don't know - what are You looking for?
... except a macro ... but without verified expected results ... all results would be as good as random results.
 
ThrottleWorks said:
Reference file.
Click to expand...
Hi, according to your attachment logic a beginner level VBA demonstration for starters :​
Code: 
Sub Demo1()
        Dim V, L&, R&, S&
    With [A1].CurrentRegion.Rows
        V = .Value2
    For L = 2 To .Count
        If IsEmpty(V(L, 2)) Then
            For R = .Count To 2 Step -1
                If V(R, 1) = V(L, 1) And Not IsEmpty(V(R, 2)) Then S = R: If R < L Then Exit For
            Next
                If S Then V(L, 2) = V(S, 2): V(L, 3) = V(S, 3): S = 0
        End If
    Next
       .Value2 = V
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
A more advanced VBA demonstration which should be faster in case of big data :​
Code: 
Sub Demo2()
        Dim V, W, L&, R&
    With [A1].CurrentRegion.Columns("B:C").Rows
        V = .Columns(0).Value2
        W = .Value2
    For L = 2 To .Count
        If IsEmpty(W(L, 1)) Then
            For R = L - 1 To 2 Step -1
                If Not IsEmpty(W(R, 1)) Then If V(R, 1) = V(L, 1) Then W(L, 1) = W(R, 1): W(L, 2) = W(R, 2): Exit For
            Next
            If R = 1 Then
                For R = L + 1 To .Count
                    If Not IsEmpty(W(R, 1)) Then If V(R, 1) = V(L, 1) Then W(L, 1) = W(R, 1): W(L, 2) = W(R, 2): Exit For
                Next
            End If
        End If
    Next
       .Value2 = W
    End With
End Sub
You may Like it !
​
 
An alternative :​
Code: 
Sub Demo3()
        Dim V, W, X, L&, R&
    With [A1].CurrentRegion.Columns("B:C").Rows
        V = .Columns(0).Value2
        W = .Value2
        X = .Columns(1).Value2
    For L = 2 To .Count
        If Not IsEmpty(X(L, 1)) Then
            For R = 2 To .Count
                If V(R, 1) = V(L, 1) Then _
                    If IsEmpty(W(R, 1)) Then W(R, 1) = W(L, 1): W(R, 2) = W(L, 2) Else If R > L Then Exit For
            Next
        End If
    Next
       .Value2 = W
    End With
End Sub
You should Like it !​
​
 
The ultimate version to reduce the iterations # but only with less than 65 537 data rows 'cause of TRANSPOSE limitation :​
Code: 
Sub Demo4()
        Dim V, W, X, L, S&, R&
    With [A1].CurrentRegion.Columns("B:C").Rows
        V = .Columns(0).Value2
        W = .Value2
        X = Evaluate("TRANSPOSE(ROW(2:" & .Count & "))")
    For Each L In Filter(Evaluate(Replace("TRANSPOSE(IF(ISBLANK(B2:B#),FALSE,ROW(B2:B#)))", "#", .Count)), False, False)
        If S Then S = 0: X = Filter(X, False, False): If UBound(X) < 0 Then Exit For
    For R = LBound(X) To UBound(X)
        If V(X(R), 1) = V(L, 1) Then
            S = X(R):  X(R) = False
            If IsEmpty(W(S, 1)) Then W(S, 1) = W(L, 1): W(S, 2) = W(L, 2) Else If S > L Then Exit For
        End If
    Next R, L
        .Value2 = W
    End With
End Sub
You may Like it !​
​
 
You must log in or register to reply here.
Back
Top