Excel macro counting column value with same content in another column

Question

Hi,

I have an Excel sheet which has the following structure:

Value     ID

30          1

20          1

10          1

15          2

35          2

Now, I want to write the summed values for each ID in a text file, but

my problem is: How can I reconsider the ID in the next row and add the next value in dependence of the ID? I know Ihave to loop through the sheet.

It has to be said that the values are sorted by ID (ascending), so the values concerning one ID are in succession.

At the end I want something in the text file like:

ID      Value

1        60

2        50

Anybody a suggestion?

Kind regards,

Nathalie

Answer 1

Hi, assuming the value as per your example is in column A and the ID in column B, then you have a list of your ID in column D starting in row 2, use this

=sumproduct(--(D=B:B),A:A)

coopy formula down

Answer 2

Hi Nathalie

You could use a formula like  this =SUMIF(B2:B6,1,A2:A6)

Just change the range. I'm assuming that ID is in column A and Values in column B.

Answer 3

Hi Nathalie,

A Pivot Table is the easiest way of getting this done.

Select the entire data, goto Insert > Pivot Table. Drag ID to Row Label and Value to Values.

That'd do it.

Answer 4

I think people may have missed the request to output the results to a text file, so if none of the above excellent replies quite hits the remark, here is a macro that will do the job, including writing the text file.  There are a series of Const declarations at the start of the code, change their values as needed to match your real-world workbook.  One thing this needs is an extra, currently unused column to build a unique list of the IDs in.  In the code that's set to column H, which may not be appropriate for you.  I do have a version of this that doesn't use the extra column, but it is much slower to run.  Here is the code I have:

Sub CreateTextFileOfTotalsByID()

'instructions for adding this code to a Regular code module

'can be found on this page:

' http://www.contextures.com/xlvba01.html

'

  'change these constants as needed/desired

  'the .txt file will be created/rewritten

  'into the same folder with the Excel file

  Const textFileName = "MyExcelData.txt"

  Const dataSheetName = "Sheet1"

  Const columnWithValues = "A"

  Const columnWithIDs = "B"

  Const columnForUniqueList = "H" ' any available column

  Const firstRowWithData = 2 ' assumes labels in row 1

  Const colNumForValues = 20 ' controls where Values are placed in text file

  Dim myTotals As Long ' change type (Long) to Currency or Double if needed

'end user redefinable values

'begin working variables list

  Dim buffNum As Integer ' needed for writing the text file

  Dim dataWS As Worksheet

  Dim uniqueIDs As Range

  Dim anyUniqueID As Range

  Dim valuesList As Range

  Dim IDsList As Range

  Dim lastRow As Long

  Set dataWS = ThisWorkbook.Worksheets(dataSheetName)

  lastRow = _

   dataWS.Range(columnWithValues & Rows.Count).End(xlUp).Row

  If lastRow < firstRowWithData Then

    lastRow = firstRowWithData

  End If

  'set up reference to the IDs on the data sheet

  Set IDsList = dataWS.Range(columnWithIDs & firstRowWithData & ":" _

   & columnWithIDs & lastRow)

  'clear any old data

  dataWS.Columns(columnForUniqueList & ":" & columnForUniqueList).ClearContents

  'copy the unique items in the IDs list to new column

  Range(columnWithIDs & "1:" & columnWithIDs & lastRow).AdvancedFilter _

    Action:=xlFilterCopy, CopyToRange:=Range(columnForUniqueList & 1), Unique:=True

  'set reference to the unique IDs list

  Set uniqueIDs = dataWS.Range(columnForUniqueList & "2:" & _

   dataWS.Range(columnForUniqueList & Rows.Count).End(xlUp).Address)

  'set up reference to the values on the data sheet

  Set valuesList = dataWS.Range(columnWithValues & firstRowWithData & ":" _

   & columnWithValues & lastRow)

  'set up the text file to be written to

  buffNum = FreeFile() ' get available buffer number

  Open ThisWorkbook.Path & Application.PathSeparator & textFileName _

   For Output As #buffNum

  'write out the header line

  Print #buffNum, "ID" & String(colNumForValues - Len("ID"), 32) & "Value"

  For Each anyUniqueID In uniqueIDs

    'let Excel do the heavy lifting here

    myTotals = Application.WorksheetFunction.SumIf(IDsList, anyUniqueID, valuesList)

    'send results to the text file.

    Print #buffNum, anyUniqueID & _

     String(colNumForValues - Len(anyUniqueID), 32) & myTotals

  Next ' end anyUniqueID loop

  Close #buffNum ' close the file so data shows up in it!

  'all done, do some cleanup

  Set valuesList = Nothing

  Set dataWS = Nothing

  MsgBox "Text file has been written.", vbOKOnly + vbInformation, "Task Completed"

End Sub