Populating, aggregating and sorting multiple matching values in Excel

P

Pbobas

New Member
Joined
Jun 21, 2024
Messages
4
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
Hello, I'm trying to compare 2 different Excel files which each contains product data and sales data. Sales data would consists of sales transactions of products that corresponds to a specific set of specifications and listed down per transaction. What I'd like to do is I'd like to understand from reconciling both files, who are the customers who are making the most purchases in terms of weight, and sort largest to smallest from the list of customers that I have.

Sample case : if I take one simple reference param (column H on reconcile sheet), say TSPFC400-0.588-1300 - 1600, I'd like for the customers column to return "PT. Persada Wijaya Sentosa" and "PT. Ferro Nusa Tata Laksana" based on a largest to smallest weight. Can anyone give some explanation on the quickest way to do this? I run this file on Excel 2016, so I have constraints on formulas that can be used.

Below is the dataset I am referring to:

A. Product sheet sample
BookRecon2016_rev2.xlsm
ABCDEFGHLMN
4Type of productPacking No.Secondary specThicknessWidthWeight (ton)Inside dia.Coating MassYard Transfer RemarkWidth categoryReference param
5CRCR4493206TSPCC0.581502.003.5006100K1300 - 1600TSPCC-0.582-1300 - 1600
6CRCR4492974TSPFC4000.591467.006.7106100K1300 - 1600TSPFC400-0.588-1300 - 1600
7CRCR4493055TSPFC4000.591467.006.7606100K1300 - 1600TSPFC400-0.588-1300 - 1600
8CRCR4493094TSPCC0.601717.006.5506100K>1600TSPCC-0.6->1600
9CRCR4492975TSPCC0.631382.006.3206100K1300 - 1600TSPCC-0.631-1300 - 1600
10CRCR4493053TSPCC0.631382.006.2806100K1300 - 1600TSPCC-0.631-1300 - 1600
11CRCR4493072TSPCC0.631667.004.3406100K>1600TSPCC-0.631->1600
12CRCR4493208TSPCC0.631667.008.6906100K>1600TSPCC-0.631->1600
13CRCR4493306TSPCC0.631667.008.0806100K>1600TSPCC-0.631->1600
14CRCR4492651TSPCC0.641218.509.4306100K~1300TSPCC-0.637-~1300
15CRCR4492657TSPCC0.641298.503.1806100K~1300TSPCC-0.637-~1300
16CRCR4492661TSPCC0.641298.504.1806100K~1300TSPCC-0.637-~1300
17CRCR4492681TSPCC0.641218.508.5906100K~1300TSPCC-0.637-~1300
18CRCR4492692TSPCC0.641218.509.4406100K~1300TSPCC-0.637-~1300
19CRCR4493056TSPCC0.641218.505.2206100K~1300TSPCC-0.637-~1300
20CRCR4493075TSPCC0.641572.004.0106100K1300 - 1600TSPCC-0.637-1300 - 1600
Products
Cell Formulas
RangeFormula
M5:M20M5=IF(E5="","",IF(E5<=1300,"~1300",IF(E5<=1600,"1300 - 1600",IF(E5>1600,">1600","Check"))))
N5:N20N5=C5&"-"&D5&"-"&M5
A5:A20A5=LEFT(B5,2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1:B4Cell ValueduplicatestextNO
B1:B4Cell ValueduplicatestextNO
B1:B4Cell ValueduplicatestextNO
B1:B4Cell ValueduplicatestextNO
B1:B4Cell ValueduplicatestextNO
B1:B149Cell ValueduplicatestextNO
B1:B149Cell ValueduplicatestextNO
B1:B149Cell ValueduplicatestextNO
B4Cell ValueduplicatestextNO
B4Cell ValueduplicatestextNO
B4Cell ValueduplicatestextNO
B4Cell ValueduplicatestextNO


B. Sales data sample
BookRecon2016_rev2.xlsm
ABCDEFGHIJKLMNOPQ
3No.Ship to Place NameCustomerContract No.Packaged CoilMaterial ProductCoating WeightOil TypeSpecificationThicknessWidthinside dia.outside dia.QTYTanggal KirimWidth CategoryReference Param
4147PT. Baja Sarana MultiPT BAJA ALAM SUSKES4CRX1NS0A0202CR3Y91577CR03TSPCC0.631148961011999.86453071300 - 1600TSPCC-0.631-1300 - 1600
5148PT. Baja Sarana MultiPT BAJA ALAM SUSKES4CRX1NS0A0202CR4191720CR03TSPCC0.631148961012019.9453071300 - 1600TSPCC-0.631-1300 - 1600
6150PT. Baja Sarana MultiPT BAJA ALAM SUSKES4CRX1NS0A0202CR3Y90573CR03TSPCC0.631148961011768.611453071300 - 1600TSPCC-0.631-1300 - 1600
7152PT. Baja Sarana MultiPT BAJA ALAM SUSKES4CRX1NS0A0202CR2999579CR03TSPCC0.63114896109104.25453071300 - 1600TSPCC-0.631-1300 - 1600
8153PT. Baja Sarana MultiPT BAJA ALAM SUSKES4CRX1NS0A0202CR3191991CR03TSPCC0.63114896108813.77453071300 - 1600TSPCC-0.631-1300 - 1600
9306PT. PANDAWAPT PANDAWA JAYA STEEL4CRX1NT0A0B01CR3Y90703CR03TSPCC0.631148961012209.687453101300 - 1600TSPCC-0.631-1300 - 1600
10308PT. PANDAWAPT PANDAWA JAYA STEEL4CRX1NT0A0B01CR3Y90714CR03TSPCC0.631148961011848.636453101300 - 1600TSPCC-0.631-1300 - 1600
11407PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX1NT0A0H06CR3Y91403CR03TSPCC0.631148961012265.378453171300 - 1600TSPCC-0.631-1300 - 1600
12417PT. PANDAWAPT PANDAWA JAYA STEEL4CRX1NT0A0G01CR3Y91465CR03TSPCC0.631148961011959.86453171300 - 1600TSPCC-0.631-1300 - 1600
13432PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX1NT0A0H05CR3Y90049CR03TSPCC0.63113826109755.08453181300 - 1600TSPCC-0.631-1300 - 1600
14433PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX1NT0A0H06CR3Y90709CR03TSPCC0.631148961011686.135453181300 - 1600TSPCC-0.631-1300 - 1600
15449PT. PANDAWAPT PANDAWA JAYA STEEL4CRX1NT0A0G01CR3Y91051CR03TSPCC0.631148961011949.8453181300 - 1600TSPCC-0.631-1300 - 1600
16459PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX1NT0A0H06CR3Y90711CR03TSPCC0.631148961011928.24453201300 - 1600TSPCC-0.631-1300 - 1600
17479PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX1NT0A0H01CR3Y91195CR03TSPFC4000.588146761011097.84453201300 - 1600TSPFC400-0.588-1300 - 1600
18480PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX1NT0A0H01CR3Y91328CR03TSPFC4000.588146761011208.03453201300 - 1600TSPFC400-0.588-1300 - 1600
19693PT. PANDAWAPT PANDAWA JAYA STEEL4CRX2NT0A0502CR3Y91481CR03TSPCC0.631138261011237.62453421300 - 1600TSPCC-0.631-1300 - 1600
20694PT. PANDAWAPT PANDAWA JAYA STEEL4CRX2NT0A0502CR4192497CR03TSPCC0.631138261011197.7453421300 - 1600TSPCC-0.631-1300 - 1600
21762PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX2NT0A0801CR4193002CR03TSPFC4000.58814676108823.83453441300 - 1600TSPFC400-0.588-1300 - 1600
22791PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX2NT0A0702CR4193150CR03TSPFC4000.58814676108793.67453461300 - 1600TSPFC400-0.588-1300 - 1600
23989PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX3NT0A0502CR3Y91519CR03TSPCC0.63114896109635.17453731300 - 1600TSPCC-0.631-1300 - 1600
24990PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX3NT0A0502CR3Y91552CR03TSPCC0.63114896109535.15453731300 - 1600TSPCC-0.631-1300 - 1600
25991PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX3NT0A0502CR4192584CR03TSPCC0.631148961011952.84453731300 - 1600TSPCC-0.631-1300 - 1600
26992PT Bangun Era Sejahtera MandiriPT PERSADA WIJAYA SENTOSA4CRX3NT0A0502CR4192594CR03TSPCC0.631148961011823.759453731300 - 1600TSPCC-0.631-1300 - 1600
271591PT Bangun Era Sejahtera MandiriPT BANGUN ERA SEJAHTERA MANDIRI4CRX5NT0A0401CR4492975CR03TSPCC0.631138261010446.32454271300 - 1600TSPCC-0.631-1300 - 1600
281592PT Bangun Era Sejahtera MandiriPT BANGUN ERA SEJAHTERA MANDIRI4CRX5NT0A0401CR4493053CR03TSPCC0.631138261010506.28454271300 - 1600TSPCC-0.631-1300 - 1600
291600PT. BMS (SMM)PT FERRO NUSA TATA LAKSANA4CRX5NT0A0301CR4492974CR03TSPFC4000.588146761010526.71454271300 - 1600TSPFC400-0.588-1300 - 1600
301601PT. BMS (SMM)PT FERRO NUSA TATA LAKSANA4CRX5NT0A0301CR4493055CR03TSPFC4000.588146761010506.76454271300 - 1600TSPFC400-0.588-1300 - 1600
Sheet4


C. Reconcile sheet (what I got so far)
BookRecon2016_rev2.xlsm
ABCDEFGHIJKLMNOPQ
4No.Packaged CoilProduct TypeSecondary specThicknessWidthWidth categoryReference paramWeight (ton)Inside dia.Coating MassYard Transfer RemarkCustomer 1Customer 2Customer 3Customer 4Customer 5
51CR4493206CRTSPCC0.58215021300 - 1600TSPCC-0.582-1300 - 16003.56100KPT PERSADA WIJAYA SENTOSAPT PERSADA WIJAYA SENTOSAPT NUR IRON STEEL  
62CR4492974CRTSPFC4000.58814671300 - 1600TSPFC400-0.588-1300 - 16006.716100KPT PERSADA WIJAYA SENTOSAPT PERSADA WIJAYA SENTOSAPT PERSADA WIJAYA SENTOSAPT PERSADA WIJAYA SENTOSAPT FERRO NUSA TATA LAKSANA
73CR4493055CRTSPFC4000.58814671300 - 1600TSPFC400-0.588-1300 - 16006.766100KPT PERSADA WIJAYA SENTOSAPT PERSADA WIJAYA SENTOSAPT PERSADA WIJAYA SENTOSAPT PERSADA WIJAYA SENTOSAPT FERRO NUSA TATA LAKSANA
84CR4493094CRTSPCC0.61717>1600TSPCC-0.6->16006.556100KPT DELI STEEL JAYA LANGGENG ABADIPT BAJA ALAM SUSKESPT PERSADA WIJAYA SENTOSAPT PERSADA WIJAYA SENTOSAPT PERSADA WIJAYA SENTOSA
95CR4492975CRTSPCC0.63113821300 - 1600TSPCC-0.631-1300 - 16006.326100KPT BAJA ALAM SUSKESPT BAJA ALAM SUSKESPT BAJA ALAM SUSKESPT BAJA ALAM SUSKESPT BAJA ALAM SUSKES
106CR4493053CRTSPCC0.63113821300 - 1600TSPCC-0.631-1300 - 16006.286100KPT BAJA ALAM SUSKESPT BAJA ALAM SUSKESPT BAJA ALAM SUSKESPT BAJA ALAM SUSKESPT BAJA ALAM SUSKES
Recon
Cell Formulas
RangeFormula
A5:A10A5=IF(B5="","",ROW(B5)-4)
B5:B10B5=IFERROR(INDEX(Products!$B$5:$B$500,MATCH(0,COUNTIF($B$4:B4,Products!$B$5:$B$500),0)), "")
C5:C10C5=IF(ISBLANK(IF(LEFT(B5,2)="0","",LEFT(B5,2))),"",IF(LEFT(B5,2)="0","",LEFT(B5,2)))
D5:D10D5=IFERROR(VLOOKUP($B5,Products!$B:$L,2, 0),"")
E5:E10E5=IFERROR(VLOOKUP($B5,Products!$B:$L,3, 0),"")
F5:F10F5=IFERROR(VLOOKUP($B5,Products!$B:$L,4, 0),"")
G5:G10G5=IF(F5="","",IF(F5<=1300,"~1300",IF(F5<=1600,"1300 - 1600",IF(F5>1600,">1600","Check"))))
H5:H10H5=D5&"-"&E5&"-"&G5
I5:I10I5=IFERROR(VLOOKUP($B5,Products!$B:$L,5, 0),"")
J5:J10J5=IFERROR(VLOOKUP($B5,Products!$B:$L,6, 0),"")
K5:K10K5=IFERROR(VLOOKUP($B5,Products!$B:$L,7, 0),"")
L5:L10L5=IFERROR(VLOOKUP($B5,Products!$B:$L,11, 0),"")
M5:Q10M5=IFERROR(INDEX(Sales!$C$3:$C$4000,SMALL(IF($H5=Sales!$Q$3:$Q$4000,ROW(Sales!$C$3:$C$4000)-ROW(Sales!$C$3)+1),COLUMN(B2)-1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.


EDIT:
I think I might have caused a bit of confusion on the above question, so to clear up, what I'm struggling to do is have distinct values of the customers returned on column M - Q on reconcile sheet, instead of multiple returned values of the same customers. Secondly, I would like to be able to sort the order of those distinct values from either largest to smallest, or smallest to largest based on the weight of products purchased referring to historical sales data extracted from the Sales sheet.
 
Last edited by a moderator:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
You must log in or register to reply here.

Similar threads

mehidy1437
  • Question Question
Correction needed in formula INDEX AGGREGATE
Replies
3
Views
770
Fluff
Fluff

Forum statistics

Threads
1,223,881
Messages
6,175,159
Members
452,615
Latest member
bogeys2birdies

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