Wednesday, February 24, 2016

Macro to write a formula based on two cells being equal Tech Support

Macro to write a formula based on two cells being equal

I have several columns of data.  The first contains a number for the incident.  Another contains a code.  There can be duplicates in the first column for different codes in the second.  There are other columns that do not get invloved in this.
Example:
A                   B
123               CC
123               DD
134               CC
134               EE
134               FF
 
I would like to forthe table to end up like this.
Example:
A                   B
123              CC, DD
134               CC, EE, FF

Solutions to the Problem Macro to write a formula based on two cells being equal

Download SmartPCFixer for Free Now

Hi,
 
here's a macro to do it.
Alt+F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste the code below in and run it
 
Sub summarise_Data()
Dim LastRow As Long, c As Range, r As Range
Dim LastColumn As Long, IncCode As String
Dim MyRange as range, x as long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
               SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
    IncCode = c.Offset(, 1) & ","
    For x = c.Row + 1 To LastRow
        If Cells(x, 1) = c.Value Then
        IncCode = IncCode & Cells(x, 1).Offset(, 1) & ","
        Cells(x, LastColumn + 1) = "#N/A"
        End If
    Next
    c.Offset(, 1) = Left(IncCode, Len(IncCode) - 1)
    incode = ""
Next
On Error Resume Next
Columns(LastColumn + 1).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End Sub

Machine Throttling
  • Go to the Start button.
  • In the Search box, type "regedit" (without quotes) and press Enter.
  • Look for the following registry key: "HKEY_LOCAL_MACHINE\SOFTWARE\ Microsoft\ SQMClient\Windows\DisabledSessions".
  • On the right window pane, delete the subkey: "Machine Throttling".
  • Reboot your computer.

Recommended Method to Repair the Problem: Macro to write a formula based on two cells being equal:

 

 

How to Fix Macro to write a formula based on two cells being equal with SmartPCFixer?

1. Download SmartPCFixer. Install it on your system.

2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All.

3. The Fixing part is finished, the speed of your computer will be much higher than before and the errors have been fixed.


Related: Time I Try To Open My Internet Explorer It Says (Not Responding),No System Maintenance Icon In Control Panel,How I Put Two Ip In Two Lan Card In Win 7,Excel 2007 Copying Cells Into Filtered Rows,Detect When User Adds New Column To Sheet,Can't Type Quotes In Some Forums,Im Looking For A Formula To SORT & Match To Get Max,Part II - Need Help With Nested IF(AND Statements,Task Manager (and Perfmon) Report ~50% CPU But Processes List Says System Idle Process 96%,Cannot Uninstall Juno Internet Software From Windows 7 Enterprise 32
Read More: Low performance issues when moving from Windows 7 Ultimate R,How to Fix - I cant Get my pc to restore back to a date?,Troubleshoot:FSX troubles with Activation Key! Error,How to Fix Problem - Unable to uninstall Adobe air which in turn is not letting any other program installations?,Troubleshooting:original cell location (identity),USB Mouse freezing,IE8 Auto Complete not working,How to get a "Text to Columns" macro and other macro to work together?,IE not working message pos up when trying to attach a photo file to email,Help with password protected file

No comments:

Post a Comment