Compare column A & B give results in column C
Solved/Closed
Related:
- Compare column A & B give results in column C
- Beyond compare - Download - File management
- Display two columns in data validation list but return only one - Guide
- How to delete column in word - Guide
- Tweetdeck remove column - Guide
- Excel count occurrences of string in column - Guide
1 response
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.
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.
