UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Finding Two Like Numbers In One Column Then Moving To Another Column And Adding Up The Two Matching Rows, Office 2007    
 
   
KathCobb
post Jan 23 2020, 05:00 PM
Post#1



Posts: 516
Joined: 18-October 06




I'm not sure if this can be done or not. What I am trying to do is this: I have a multi-column spreadsheet that has each persons ID number in one column--twice. For example ID Number: FY123 appears in C6 and C7. Not every single ID number is repeated. The reason for this is that there is a dollar amount associated with each ID number, but they have different reasons for being broken out. I'm trying to find a way for excel to find two matching ID numbers, remember those two rows (cells?) move over 5 columns to the right, and add the two numbers in the same rows and place the result in the next column over. Here is a small example:

Some info Some info Some info ID Name Some info Some info Some info Some info Some info Some info Amount My Result
xxxxxxxxx xxxxxxxxx xxxxxxxxx 22341831 John xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx $27.25
xxxxxxxxx xxxxxxxxx xxxxxxxxx 22341831 John xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx $16.67 $43.92
xxxxxxxxx xxxxxxxxx xxxxxxxxx 21525774 Jeff xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx $21.25
xxxxxxxxx xxxxxxxxx xxxxxxxxx 21525774 Jeff xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx $19.00 $40.25
xxxxxxxxx xxxxxxxxx xxxxxxxxx 21878455 Jane xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx $21.25
xxxxxxxxx xxxxxxxxx xxxxxxxxx 22915443 Jill xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx $21.25
xxxxxxxxx xxxxxxxxx xxxxxxxxx 22604811 Betty xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx $21.25
xxxxxxxxx xxxxxxxxx xxxxxxxxx 22604811 Betty xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx xxxxxxxxx $14.00 $35.25

There isn't any unique vales in the "some info" columns. Its known to us what the separate lines mean. There can be thousands of rows. So I can't say here is a list of ID's then match them, etc. It has to find the "duplicates", I guess is the correct word, then move over and add the values in the amount column and place the results in the column next to it. The IDs that do not have duplicates can either be ignored or just put that result of that plus nothing in the result column. Whatever is an easier task.

Like I said, I am not sure if this can even be done in vba or with a function(my function knowledge is very limited and my vba is basic).
Any help would be appreciated.

Thank you,
Kathy

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
WildBird
post Jan 23 2020, 06:03 PM
Post#2


UtterAccess VIP
Posts: 3,724
Joined: 19-August 03
From: Auckland, Little Australia


A simple way, if you have MS Access, is to link to it, and do a Totals query and sum the amount field.

If it has to be done in Excel, that is a bit different.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
KathCobb
post Jan 23 2020, 07:35 PM
Post#3



Posts: 516
Joined: 18-October 06



It has to be done in Excel. I tried using sub totals function but I can only total under the amount and I really need it in an adjacent column.

Is there a vba code for the subtotals function that could be altered? I tried the macro recorder but that didn’t really show me how to have it find the specific matches just the formula of how to have the total appear in the appropriate column.

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
June7
post Jan 23 2020, 09:16 PM
Post#4



Posts: 1,235
Joined: 25-January 16
From: The Great Land


See if this gets you started. Code in module behind worksheet. Tested with provided sample data.
CODE
Sub CalcSubTot()
Dim r As Integer, strName As String, dblAmt As Double
r = 2
Do While Not IsEmpty(Range("E" & r))
    strName = Range("E" & r)
    dblAmt = Range("L" & r)
    r = r + 1
    If Range("E" & r) = strName Then Range("M" & r) = dblAmt + Range("L" & r)
Loop
End Sub


This post has been edited by June7: Jan 23 2020, 09:17 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Vince
post Jan 24 2020, 02:36 AM
Post#5



Posts: 75
Joined: 18-August 16
From: Bristol, UK


Try:

=SUMIFS(L:L,D:D,"=" & D2)

where ID is column D and amount is column L.
Go to the top of the page
 
June7
post Jan 24 2020, 03:38 AM
Post#6



Posts: 1,235
Joined: 25-January 16
From: The Great Land


Nice! It does mean a value returns to every cell in column M. So if intent is to show total under that column, would have to actually sum column L otherwise some subtotals would be added twice.

Correction to my code, should have referenced column D instead of E.

This post has been edited by June7: Jan 24 2020, 03:39 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Vince
post Jan 24 2020, 05:36 AM
Post#7



Posts: 75
Joined: 18-August 16
From: Bristol, UK


If you don't want them repeated and shown at the first occurrence of the reference then try:

=IF(ISERROR(MATCH(D2,INDIRECT("d1:d" & (ROW()-1)),0)), SUMIFS(L:L,D:D,"=" & D2),"")
Go to the top of the page
 
Vince
post Jan 29 2020, 03:10 AM
Post#8



Posts: 75
Joined: 18-August 16
From: Bristol, UK


I've had a bit of a re-think on this as from your example it looks like you want the result on the last occurrence of the reference and do not want to include an entry if there is only one occurrence.

To determine if last occurrence count the number of references below the current row:

COUNTIF(INDIRECT("D" & ROW()+1 & ":D1048576"),D2)

This will be zero if the last entry. Note 1048576 should be the last row in the sheet, however this could change depending on your version of Excel.

Also count the total number of references to see if multiple occurrences:

COUNTIF(D:D,D2)

Combine together:

=IF(AND(COUNTIF(INDIRECT("D" & ROW()+1 & ":D1048576"),D2)=0,COUNTIF(D:D,D2)>1),SUMIFS(L:L,D:D,"=" & D2),"")

Go to the top of the page
 
KathCobb
post Jan 29 2020, 01:43 PM
Post#9



Posts: 516
Joined: 18-October 06




Sorry it took me so long to get back. As a follow up, The code June7 provided worked perfectly!! Thank you ever so much! I used the column letters specific to my actual sheet. It also did not put any total in the column for IDs that only occurred one, which is exactly what I wanted. This will save me so much time notworthy.gif

Can that code be modified to insert a row below each total?

Also could a similar code be used to find all the occurrences of one number then after the last occurrence insert a row? They are all sorted already by number so they would be grouped together.

Thank you again for all the help

Kathy

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th February 2020 - 03:05 AM