index-match formula produces duplicates from SUMIFS data

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
Hi Folks,

I have the following conundrum which I could use your help with please!

  1. titles in column G are duplicated as the values in column H are the same. The second value in column G should be R2 routine saw change.
  2. I can't use the various online tips such as assigning random numbers to the dataset and looking those up instead, as the dataset is produced using a SUMIFS formula which adds values with like categories in another table.
See snips:

1623335365895.png


This is from the 'data' tab:

1623335408755.png


Any help will be greatly appreciated :)

Jon
 
Last edited by a moderator:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post a sample of your data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
It says 365, however I thought 365 was the online version. In any case, it is the desktop version on windows 10.

WHich portion of the data would be most useful for solving this issue?

Thanks for your help :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Ok, if you remove the formulae in G21:H25 & put this in G21
Excel Formula:
=INDEX(FILTER(SORT(A3:C200,3,-1),{1,0,1}),SEQUENCE(5),{1,2})
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, if you remove the formulae in G21:H25 & put this in G21
Excel Formula:
=INDEX(FILTER(SORT(A3:C200,3,-1),{1,0,1}),SEQUENCE(5),{1,2})
Thank you for this- this works perfectly for the weekly range.

If I copy the formula into G13, and change the formula to use column number 1, it spills into column H.:

1623657626928.png
1623657653436.png


Any further ideas?

XL2BB:

Downtime Input- Rosens.xlsm
BCDEFGHI
1May-2131/05/20212021
2MonthlyWeekly Yearly
3255.0040.00295.00Yearly
420.000.0020.00AreaAmountPercentage
5150.000.00150.00R2 Machine Setup485.0018.14%
6210.0075.00285.00R2 Cleaning365.0013.66%
790.000.0090.00R1 Cleaning295.0011.04%
828.0040.0068.00R1 Machine Setup285.0010.66%
960.000.0060.00R2 Out of Routine Saw Change190.007.11%
10125.000.00125.00
110.000.000.00Monthly May
120.0030.0030.00AreaAmountPercentage
13200.00165.00365.00R2 CleaningR2 Cleaning#VALUE!
1480.0055.00135.00R2 Machine SetupR2 Machine Setup#VALUE!
1535.000.0035.00R2 Routine Saw ChangeR2 Routine Saw Change#VALUE!
16395.0090.00485.00R1 Machine SetupR1 Machine Setup#VALUE!
1765.000.0065.00R2 Clear RejectsR2 Clear Rejects#VALUE!
Graphs & Top 5
Cell Formulas
RangeFormula
B3:B17B3=SUMIFS(Data!C:C,Data!H:H,'Graphs & Top 5'!B$1,Data!B:B,'Graphs & Top 5'!A3)
C3:C17C3=SUMIFS(Data!C:C,Data!I:I,'Graphs & Top 5'!C$1,Data!B:B,'Graphs & Top 5'!A3)
D3:D17D3=SUMIFS(Data!C:C,Data!J:J,'Graphs & Top 5'!D$1,Data!B:B,'Graphs & Top 5'!A3)
G5:G9G5=INDEX($A:$A,MATCH(H5,$D:$D,0))
H5H5=LARGE($D$3:$D$200,1)
I5:I9I5=H5/SUM($D$3:$D$200)
H6H6=LARGE($D$3:$D$200,2)
H7H7=LARGE($D$3:$D$200,3)
H8H8=LARGE($D$3:$D$200,4)
H9H9=LARGE($D$3:$D$200,5)
I11I11=B1
G13:H17G13=INDEX(FILTER(SORT(A3:C200,3,-1),{1,0,1}),SEQUENCE(5),{1,1})
I13:I17I13=H13/SUM($B$3:$B$200)
Dynamic array formulas.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
For monthly use
Excel Formula:
=INDEX(SORT(A3:B200,2,-1),SEQUENCE(5),{1,2})
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
Amazing, thanks for this. DIdn't think there was going to be a way to do this but you've proven me wrong.

Thanks again :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,062
Messages
5,704,060
Members
421,325
Latest member
tapete86

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
Top