Home > Forum Home > Presenting and Reporting Data > ADP Report Share

ADP Report

Excel Help for Adp Report in Presenting and Reporting Data


Forum TopicPost Reply Login

Adp Report

Rate this:
(3/5 from 1 vote)
ConfusedWhen I pull data from my ADP system it puts several dollar amounts into one cell. Example:
20.00
5.00
10.00
All in one cell, in a stack like this. I need a way to add each cell of numbers into one number. When I try to view the numbers in the formula bar it only shows one number at a time and I have to scroll down in the formula bar to see the next number. Somehow I need to add all the numbers as a total single number. Usually I will have upwards of 250   rows of numbers to add together as stated above. Any help would be good. I can send an example of spreadsheet if needed.
 Posted by on
 
Replies - Displaying 1 to 2 of 2Order Replies By: Most Recent | Chronological | Highest Rated
Angry
Rate this:
(3/5 from 1 vote)
This is caused by line feeds within the source data that are imported as text. To avoid this, try to use alternative import options or if using a paste function then Paste Special > Unicode Text. If this is not possible then a VBA macro can split the lines feeds into separate cells. The following VBA sub routine will split values for a selected range of cells in a column to continuous rows in the adjacent column to the right.

VBA Code:
Sub LinesToCells()
    Dim cr, ac As Variant
    Dim r, c, i As Integer
    r = ActiveCell.Row
    c = ActiveCell.Column + 1
    For Each cr In Selection
        ac = Split(cr.Text, Chr(10))
        For i = LBound(ac) To UBound(ac)
            Cells(r, c).Value = ac(i)
            r = r + 1
        Next
    Next
End Sub
 Excel Business Forums Administrator
 Posted by on
Happy
Rate this:
(3/5 from 1 vote)
To total the values in the cell with line feeds, a modified version of the VBA macro can be used which runs on all selected cells.

VBA Code:
Sub LinesToTotal()
    Dim cr, ac As Variant
    Dim i As Integer
    Dim t As Single
    On Error Resume Next
    For Each cr In Selection
    t = 0
        ac = Split(cr.Text, Chr(10))
        For i = LBound(ac) To UBound(ac)
            t = t + Trim(ac(i))
        Next
        cr.Formula = t
    Next
End Sub
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for ADP Report in the