Ask a question »

Excel - Compare column A & B give results in column C

July 2015


Excel - Compare column A & B give results in column C




Issue


I have Data in Column A & B which runs up to 55000 columns sometimes 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.

Solution 1


This Is the first macro you can use:

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 

Solution 2


Another macro:

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 

Solution 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.

Note


Thanks to Navaneeth for this tip on the forum.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-compare-column-a-b-give-results-in-column-c.pdf

See also

In the same category

Published by aakai1056.
This document entitled « Excel - Compare column A & B give results in column C » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.