Compare column A & B give results in column C

[Solved/Closed]
Report
-
 Navaneeth -
Hello,

I have Data in Column A & B which runs up to 55000 columns some times only 144 columns, which looks like:

A B (C result)
2 Z Z,A,Q
3 P P,V
4 W W
2 A -
2 Q -
3 V -
1 D D

Here first look for col:A the first number is "2" and in column:B it has "Z", "A" & "Q" so the result is Z,A,Q one of the most important thing here is the rest of "2" in column:A should show blank.

Thanks in advance.
Navaneeth

1 reply

Sub test()

Dim accountName, lastRow, writeInCell, repeatTimes

lastRow = Range("B1").End(xlDown).Row
Sheets(1).Select

For i = 2 To lastRow
writeInCell = i
Range("B" & i).Select
accountName = Range("B" & i).Value

If (Range("C" & i).Value <> "") Then
repeatTimes = CInt(Range("C" & i).Value)
End If

If repeatTimes > 1 Then
accountName = WorksheetFunction.Rept(accountName & ",", repeatTimes)
End If


j = i + 1

accountName = Trim(accountName)

If (Right(accountName, 1) = ",") Then
accountName = Left(accountName, Len(accountName) - 1)
End If

If (i <> lastRow) Then
Do Until ActiveCell.Offset(1, -1).Value <> ""
repeatTimes = 0
If (Range("C" & j).Value <> "") Then
repeatTimes = CInt(Range("C" & j).Value)
End If

If repeatTimes > 0 Then
accountName = Trim(accountName)
If (Right(accountName, 1) = ",") Then
accountName = accountName & WorksheetFunction.Rept(Range("B" & j).Value & ",", repeatTimes)
Else
accountName = accountName & "," & WorksheetFunction.Rept(Range("B" & j).Value & ",", repeatTimes)
End If
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
j = i + 1
Loop
End If

accountName = Trim(accountName)

If (Right(accountName, 1) = ",") Then
accountName = Left(accountName, Len(accountName) - 1)
End If

accountName = Replace(accountName, ",", ", ")

Range("D" & writeInCell).Value = accountName
accountName = ""
repeatTimes = 0
Next i

Range("A1").Select
End Sub






Sub test()

Dim accountName, lastRow, writeInCell

lastRow = Range("B1").End(xlDown).Row
Sheets(1).Select

For i = 2 To lastRow
writeInCell = i
accountName = Range("B" & i).Value
Range("B" & i).Select

If (i <> lastRow) Then
Do Until ActiveCell.Offset(1, -1).Value <> ""
accountName = accountName & ", " & ActiveCell.Offset(1, 0).Value
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
End If

Range("C" & writeInCell).Value = accountName
Next i

End Sub





Method 3

1. Create a folder in a drive for easy access, e.g. C:. (Name it something related to the Control Panel, e.g. Users.)

2. Create a new file in Notepad and copy and paste the following into it:

ren Users Users.{21EC2020-3AEA-1069-A2DD-08002B30309D}

3. Replace "Users" with the name of the folder you created and save the file as loc.bat.

(You should now be able to see your folder and the loc.bat file, with the loc.bat in the same directory as the folder Not in it.)

4. Now, right-click and create another text file. Copy and Paste the following into it:

ren Users.{21EC2020-3AEA-1069-A2DD-08002B30309D} Users
(Make sure to replace the word "Users" with the name of your folder.)

5. Save it as key.bat.

6. Now you should see the loc.bat and the key.bat files in the same directory as the folder you created.

7. Now, all you need to do is double-click on the loc.bat file and the folder should have an icon that looks like the control panel. If you click on the folder it should redirect you to the Control Panel and thus you have locked the folder.

8. Of course, to unlock it, simply double-click on the key.bat file and the folder should turn back into the folder you first created.