Look up to Duplicate cells and return the sum total of each unique duplicate cell

D

dhome

New Member
Joined
Dec 28, 2016
Messages
47
Good afternoon to everyone,

I hope your all getting used to the new normal while we try to counteract COVID19.

I have two sheets

Here is a small summary of my data set

Sheet 1 - My lookup is in column A 0054 and my formula is in column F - 137.70

Sheet 2 - is the table array with duplicate values Column A:B

I would like a formula to lookup and sum duplicate reference numbers so sheet A in this example ref 0054 would agree to the total of 497.70 and would pull that amount into the cell and not 137.70



Sheet 1
0054497.708/09/2020CLAIRE137.7CHECK



Sheet 2

0054137.7Sales PaymentFaststat Limited
497.7​
check
0054360Sales PaymentSKS
497.7​
check


Thank you for your help
 
Sort by date Sort by votes
I though you were running this on two separate sheets. :unsure:
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I am

Sheet one is called Bank Summary this is the one with sequential refs 1-2500

Sheet two is called Bank Download this is the one with duplicate refs 1-2500 but there may be four rows of 54, three rows of 25 etc
 
Upvote 0
The formula you showed is looking at a sheet called Nominal Download, yet you are now saying that's the wrong sheet?
Where are you trying to put the formula & what cells should it look and on what sheet.
 
Upvote 0
Hi

Bank Summary Sheet the range is D2:D2500 ( This column has the sequential refs no duplicates) my formula is in J2 on this sheet

Nominal Download the range is G2:H5000 where column G holds the references 1-2500 with duplicates and H has values

Thank you
 
Upvote 0
In that case use
Excel Formula: 
 =SUMIFS('Nominal Download'!H:H,'Nominal Download'!G:G,G2)
 
Upvote 0
See works until it comes up against a duplicate record on the other sheet

DateNarrative #1Narrative #2ReferencesDebitCreditDatePostedFilter
01/09/2020H JENKINSON & CO, ADBOARDS LTD923 - BANK TRANSFER - CREDIT3361208/09/2020CLAIRE612OK
01/09/2020ALLSORTS OFF LTD, ALLSORTS923 - BANK TRANSFER - CREDIT34253.308/09/2020CLAIRE253.3OK
01/09/2020HAMPSHIRE COUNTY C, 1000 9705139547 K923 - BANK TRANSFER - CREDIT35284108/09/2020CLAIRE2841OK
01/09/2020EASTERN SHIRES PUR, 957661923 - BANK TRANSFER - CREDIT361141.3408/09/2020CLAIRE1141.34OK
01/09/2020BRADY CORP. LTD, SI1041041923 - BANK TRANSFER - CREDIT37189.3408/09/2020CLAIRE189.34OK
01/09/2020EURO BUSINESS FROM C & I C EDSON T/A E B S923 - BANK TRANSFER - CREDIT38482.5208/09/2020CLAIRE482.52OK
01/09/2020ZENOFFICE FROM ZENOFFICE LIMITED923 - BANK TRANSFER - CREDIT392331.4208/09/2020CLAIRE2331.42OK
01/09/2020KEELEY TR 1004115 FROM KEELEY-TRAVIS BUSS923 - BANK TRANSFER - CREDIT40571.9208/09/2020CLAIRE571.92OK
01/09/2020SI1041180 FROM GLEBE FARM FOODS L923 - BANK TRANSFER - CREDIT4189.1208/09/2020CLAIRE89.12OK
01/09/2020FROM SL-MOTO : S WICKENS ACCT 1012536923 - BANK TRANSFER - CREDIT4228.208/09/2020CLAIRE28.2OK
01/09/2020CHRISBEON FROM CHRISBEON OFF TEL923 - BANK TRANSFER - CREDIT43393.4108/09/2020CLAIRE393.41OK
01/09/2020SI1041527 FROM TASK BUSINESS SUPP923 - BANK TRANSFER - CREDIT4444.6408/09/2020CLAIRE44.64OK
01/09/2020EGAN REID EGAN001 FROM EGAN REID STATIO923 - BANK TRANSFER - CREDIT451426.5908/09/2020CLAIRE1426.59OK
01/09/2020Boards Direct FROM PAUL DAVID923 - BANK TRANSFER - CREDIT466312.9308/09/2020CLAIRE6312.93OK
01/09/2020LONGFELLOW FROM Longfellow Office Supplies Limited923 - BANK TRANSFER - CREDIT47174.2408/09/2020CLAIRE174.24OK
02/09/2020UNIQUE OFFICE SL, A/C UNIQ001923 - BANK TRANSFER - CREDIT481855.4408/09/2020CLAIRE1855.44OK
02/09/2020PROACTIVE, P923 - BANK TRANSFER - CREDIT492078.408/09/2020CLAIRE2078.4OK
02/09/2020BRADY CORP. LTD, SI1041053923 - BANK TRANSFER - CREDIT5062.9708/09/2020CLAIRE62.97OK
02/09/2020ELGRA FURNITURE CO, INVOICES923 - BANK TRANSFER - CREDIT511837.6208/09/2020CLAIRE1837.62OK
02/09/2020ENFIELD SAFETY SUP FROM ENFIELD SAFETY SUP923 - BANK TRANSFER - CREDIT521814.7208/09/2020CLAIRE1814.72OK
02/09/2020ATIC001 FROM ATIC GROUP LIMITED923 - BANK TRANSFER - CREDIT53296.408/09/2020CLAIRE296.4OK
02/09/2020FROM SL-MOTO : ACCT 1001757 £137.70, ACCT 1010543 £360.00923 - BANK TRANSFER - CREDIT54497.708/09/2020CLAIRE497.7OK
02/09/2020OFFI004 Off Friend FROM LINC OFF923 - BANK TRANSFER - CREDIT551843.5208/09/2020CLAIRE497.7CHECK
 
Upvote 0
dhome said:
See works until it comes up against a duplicate record on the other sheet

DateNarrative #1Narrative #2ReferencesDebitCreditDatePostedFilter
01/09/2020H JENKINSON & CO, ADBOARDS LTD923 - BANK TRANSFER - CREDIT3361208/09/2020CLAIRE612OK
01/09/2020ALLSORTS OFF LTD, ALLSORTS923 - BANK TRANSFER - CREDIT34253.308/09/2020CLAIRE253.3OK
01/09/2020HAMPSHIRE COUNTY C, 1000 9705139547 K923 - BANK TRANSFER - CREDIT35284108/09/2020CLAIRE2841OK
01/09/2020EASTERN SHIRES PUR, 957661923 - BANK TRANSFER - CREDIT361141.3408/09/2020CLAIRE1141.34OK
01/09/2020BRADY CORP. LTD, SI1041041923 - BANK TRANSFER - CREDIT37189.3408/09/2020CLAIRE189.34OK
01/09/2020EURO BUSINESS FROM C & I C EDSON T/A E B S923 - BANK TRANSFER - CREDIT38482.5208/09/2020CLAIRE482.52OK
01/09/2020ZENOFFICE FROM ZENOFFICE LIMITED923 - BANK TRANSFER - CREDIT392331.4208/09/2020CLAIRE2331.42OK
01/09/2020KEELEY TR 1004115 FROM KEELEY-TRAVIS BUSS923 - BANK TRANSFER - CREDIT40571.9208/09/2020CLAIRE571.92OK
01/09/2020SI1041180 FROM GLEBE FARM FOODS L923 - BANK TRANSFER - CREDIT4189.1208/09/2020CLAIRE89.12OK
01/09/2020FROM SL-MOTO : S WICKENS ACCT 1012536923 - BANK TRANSFER - CREDIT4228.208/09/2020CLAIRE28.2OK
01/09/2020CHRISBEON FROM CHRISBEON OFF TEL923 - BANK TRANSFER - CREDIT43393.4108/09/2020CLAIRE393.41OK
01/09/2020SI1041527 FROM TASK BUSINESS SUPP923 - BANK TRANSFER - CREDIT4444.6408/09/2020CLAIRE44.64OK
01/09/2020EGAN REID EGAN001 FROM EGAN REID STATIO923 - BANK TRANSFER - CREDIT451426.5908/09/2020CLAIRE1426.59OK
01/09/2020Boards Direct FROM PAUL DAVID923 - BANK TRANSFER - CREDIT466312.9308/09/2020CLAIRE6312.93OK
01/09/2020LONGFELLOW FROM Longfellow Office Supplies Limited923 - BANK TRANSFER - CREDIT47174.2408/09/2020CLAIRE174.24OK
02/09/2020UNIQUE OFFICE SL, A/C UNIQ001923 - BANK TRANSFER - CREDIT481855.4408/09/2020CLAIRE1855.44OK
02/09/2020PROACTIVE, P923 - BANK TRANSFER - CREDIT492078.408/09/2020CLAIRE2078.4OK
02/09/2020BRADY CORP. LTD, SI1041053923 - BANK TRANSFER - CREDIT5062.9708/09/2020CLAIRE62.97OK
02/09/2020ELGRA FURNITURE CO, INVOICES923 - BANK TRANSFER - CREDIT511837.6208/09/2020CLAIRE1837.62OK
02/09/2020ENFIELD SAFETY SUP FROM ENFIELD SAFETY SUP923 - BANK TRANSFER - CREDIT521814.7208/09/2020CLAIRE1814.72OK
02/09/2020ATIC001 FROM ATIC GROUP LIMITED923 - BANK TRANSFER - CREDIT53296.408/09/2020CLAIRE296.4OK
02/09/2020FROM SL-MOTO : ACCT 1001757 £137.70, ACCT 1010543 £360.00923 - BANK TRANSFER - CREDIT54497.708/09/2020CLAIRE497.7OK
02/09/2020OFFI004 Off Friend FROM LINC OFF923 - BANK TRANSFER - CREDIT551843.5208/09/2020CLAIRE497.7CHECK
Click to expand...
sheet Nominal Download

Account PeriodAccount ReferenceTransaction ReferenceTransaction DateAlternativeReference_AlternativeReferenceStage
202009SP101603801/09/202033612.00Sales Payment
202009SP101603901/09/202034253.30Sales Payment
202009SP101604001/09/2020352841.00Sales Payment
202009SP101604101/09/2020361141.34Sales Payment
202009SP101604201/09/202037189.34Sales Payment
202009SP101604301/09/202038482.52Sales Payment
202009SP101604401/09/2020392331.42Sales Payment
202009SP101604501/09/202040571.92Sales Payment
202009SP101605901/09/20204189.12Sales Payment
202009SP101606001/09/20204228.20Sales Payment
202009SP101606101/09/202043393.41Sales Payment
202009SP101606201/09/20204444.64Sales Payment
202009SP101606301/09/2020451426.59Sales Payment
202009SP101606401/09/2020466312.93Sales Payment
202009SP101606501/09/202047174.24Sales Payment
202009SP101606602/09/2020481855.44Sales Payment
202009SP101606702/09/2020492078.40Sales Payment
202009SP101606802/09/20205062.97Sales Payment
202009SP101606902/09/2020511837.62Sales Payment
202009SP101607002/09/2020521814.72Sales Payment
202009SP101607102/09/202053296.40Sales Payment
202009SP101607202/09/202054137.70Sales Payment
202009SP101607302/09/202054360.00Sales Payment
202009SP101607402/09/2020551843.52Sales Payment
 
Upvote 0
Which columns are those values in?
It would be better to post your data using the XL2BB add-in using the "Generate output", as that way we can see what columns are what.
 
Upvote 0
Bank Summary A to K the formula is in J

Nominal Download A to I duplicate ref in G and the value in H

Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Take duplicate consecutive cell values and sum their offset?
2
Replies
10
Views
561
awoohaw
awoohaw
O
  • Solved
Macro to scan multiple columns and find total sum of each unique item on a list of unique values
2
Replies
11
Views
1K
Oraekene
O
N
  • Question
Easiest way to get total.
Replies
3
Views
309
awoohaw
awoohaw
G
  • Question
My Index match is not looking up all the rows. It is stopping at the first returned match.
Replies
2
Views
7K
AhoyNC
A
M
  • Question
Sum If Cell Matches
Replies
4
Views
564
Fluff
Fluff

Forum statistics

Threads
1,215,779
Messages
6,126,846
Members
449,343
Latest member
DEWS2031

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top