Removing duplicates and adding amounts

Ramballah

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
334
Office Version
  1. 365
Platform
  1. Windows
Hi,

So the past weeks I've been making a new workbook for work and received amazing help from the people of this community.
And right now I'm on my last puzzle!

I have this power query that reformats information from the raw data for me and works wonders (power query is because the data is dynamic as I add new rows of data every week)
And now I need to have a second table right next to it to basically tell me which items have the highest amount, and tell me how many times each item appeared.
Here is the example:
I have this:
DateWkCodeNameAmount Totaal TypeGroup
22/07/20243023077BLIJKI VRIJ UITL OMEGA 35€ 13,65OvercodeEieren
22/07/202430432527OPTIME DRINK BANAAN 1L1€ 1,99OvercodeDrink
22/07/202430178334DANONE SPRINKLINS 2P1€ 1,89OvercodeEet
22/07/202430209076ARLA LACTOFREE HV YOGHURT2€ 4,50OvercodeEet
22/07/202430499687I SKYR SKYR TOP CARAMEL2€ 5,46OvercodeN.V.T.
22/07/202430578882DANONE M&M YOGHURT4€ 4,52OvercodeEet
23/07/20243065689OPTIME DRINK AARDB/KERS1€ 1,95OvercodeDrink
23/07/20243074004CAMPIN HV MELK 2.4 L1€ 2,90OvercodeDrink
23/07/202430347824OPTIME DRINK AARDBKERS1.52€ 5,12OvercodeDrink
23/07/202430347825OPTIME DRINK FRAMBS 1.5L1€ 2,61OvercodeDrink
23/07/202430594245ACTIME SINAASAPPEL 4PK9€ 21,06OvercodeDrink
23/07/202430121010MONA PUDDING TOETJE VDM2€ 5,08OvercodeEet
23/07/202430174433LINDAHLS KVARG VANILLE2€ 5,30OvercodeEet
23/07/202430193264JUMBO KNIJPKWRK AARDB 4P1€ 1,57OvercodeEet
23/07/202430576940EHRMANN PROT PUDD CARAMEL5€ 8,60OvercodeEet
23/07/202430576944EHRMANN PROT PUD CHOC TOP1€ 1,79OvercodeEet
23/07/202430577924ALMHOF HOEKJE VENETIE3€ 4,14OvercodeEet
23/07/202430578379ZHOEVE PROT TOP YOG AARDB2€ 3,50OvercodeEet
23/07/202430603216ALPRO PROT YOGH TROPICAL6€ 11,58OvercodeEet
23/07/202430603217ALPRO PROT YOGH ROODFRUIT12€ 23,16OvercodeEet
23/07/202430610577JUMBO SLAGROOM 35% 125ML2€ 1,98OvercodeEet
23/07/202430388397JUMBO KWARK VANILLE1€ 1,89BreukEet
24/07/202430610412SCHARREL EI WIT 301€ 6,31OvercodeEieren
24/07/20243031677CAMPIN HALFVOLLE MILDE YO1€ 1,64OvercodeEet
24/07/202430221285JUMBO BIOGARDE ROERYOGH1€ 1,39OvercodeEet
24/07/202430221285JUMBO BIOGARDE ROERYOGH1€ 1,39OvercodeEet
24/07/202430347423CAMPIN KWARK MAGER AARDB1€ 1,99OvercodeEet
24/07/202430388396JUMBO KWARK AARDBEI2€ 2,98OvercodeEet
24/07/202430526979CAMPIN VLA CHOCO1€ 1,89OvercodeEet
24/07/202430526979CAMPIN VLA CHOCO1€ 1,89OvercodeEet
24/07/202430578747JUMBO VLAATJE DUBBEL2€ 2,78OvercodeEet
24/07/202430580961VIFIT DRINK BOSVRUCHTEN3€ 4,47OvercodeEet
24/07/202430593943MELKUN PROT KWARK VANILLE2€ 2,98OvercodeEet
24/07/202430102408MELKUN HAVERMOUTPAP1€ 2,19BreukEet
24/07/202430311924NEUTR CREME FRAICHE 30%1€ 0,99BreukEet
24/07/202430578747JUMBO VLAATJE DUBBEL1€ 1,39BreukEet
24/07/202430347423CAMPIN KWARK MAGER AARDB1€ 1,99BederfEet
25/07/202430605273JUMBO GEKOOKT 2 STK2€ 1,98OvercodeEieren
25/07/202430420594JUMBO BIO VOLLE MELK 1L1€ 1,39OvercodeDrink
25/07/202430376106JUMBO VANILLE YOGHURT1€ 1,62OvercodeEet
25/07/202430577759MELKUN PROT PUDD SALT CAR1€ 1,66OvercodeEet
25/07/202430577759MELKUN PROT PUDD SALT CAR1€ 1,66OvercodeEet
25/07/202430578077ZHOEVE BOER N YOGH MU BOS4€ 6,80OvercodeEet
25/07/202430612024ZHOEVE BOERN YOGH HAGELSL3€ 5,25OvercodeEet
26/07/202430602972JUMBO GEKLEURDE EIEREN 61€ 2,49OvercodeEieren
26/07/202430610397JUMBO SCHARREL L/XL 101€ 4,05BreukEieren
26/07/202430198297OPTIME DRINK AARDBEI 1L6€ 11,34OvercodeDrink
26/07/202430501100JUMBO HV MELK 1.5L BLK4€ 6,64BreukDrink
26/07/202430501100JUMBO HV MELK 1.5L BLK1€ 1,66BreukDrink

Which is only 50 rows for now. And what I want is this:
CodeNameAmountTimesGroup
23077BLIJKI VRIJ UITL OMEGA 351Eieren
432527OPTIME DRINK BANAAN 1L11Drink
178334DANONE SPRINKLINS 2P11Eet
209076ARLA LACTOFREE HV YOGHURT21Eet
499687I SKYR SKYR TOP CARAMEL21N.V.T.
578882DANONE M&M YOGHURT41Eet
65689OPTIME DRINK AARDB/KERS11Drink
74004CAMPIN HV MELK 2.4 L11Drink
347824OPTIME DRINK AARDBKERS1.521Drink
347825OPTIME DRINK FRAMBS 1.5L11Drink
594245ACTIME SINAASAPPEL 4PK91Drink
121010MONA PUDDING TOETJE VDM21Eet
174433LINDAHLS KVARG VANILLE21Eet
193264JUMBO KNIJPKWRK AARDB 4P11Eet
576940EHRMANN PROT PUDD CARAMEL51Eet
576944EHRMANN PROT PUD CHOC TOP11Eet
577924ALMHOF HOEKJE VENETIE31Eet
578379ZHOEVE PROT TOP YOG AARDB21Eet
603216ALPRO PROT YOGH TROPICAL61Eet
603217ALPRO PROT YOGH ROODFRUIT121Eet
610577JUMBO SLAGROOM 35% 125ML21Eet
610412SCHARREL EI WIT 3011Eieren
31677CAMPIN HALFVOLLE MILDE YO11Eet
221285JUMBO BIOGARDE ROERYOGH22Eet
347423CAMPIN KWARK MAGER AARDB11Eet
388396JUMBO KWARK AARDBEI21Eet
526979CAMPIN VLA CHOCO22Eet
578747JUMBO VLAATJE DUBBEL21Eet
580961VIFIT DRINK BOSVRUCHTEN31Eet
593943MELKUN PROT KWARK VANILLE21Eet
605273JUMBO GEKOOKT 2 STK21Eieren
420594JUMBO BIO VOLLE MELK 1L11Drink
376106JUMBO VANILLE YOGHURT11Eet
577759MELKUN PROT PUDD SALT CAR22Eet
578077ZHOEVE BOER N YOGH MU BOS41Eet
612024ZHOEVE BOERN YOGH HAGELSL31Eet
602972JUMBO GEKLEURDE EIEREN 611Eieren
198297OPTIME DRINK AARDBEI 1L61Drink

It counts how many times certain items are found, so some items are found twice and adds up those qty amounts. Even singular appearances get in this 2nd table. Also, its only for the items with the type "Overcode". I don't need this for the other types only for this. This allows me to find which items are being trashed the most, and which are trashed often.
I also don't care if this is with formulas or power query or macro's. It's my own workbook! I hope this information is enough, if not feel free to ask!
Thanks in advance,
Ramballah

*edit* added a picture for reference, and some items appear like 5-10 times with different amount, in this example it just happened that only a few appeared multiple times with only 1 on the amount column. So the amounts can be 5+7 aswell for example!
1725302519457.png
 
Sort by date Sort by votes
Looks like you are missing a comma at the end of the Renamed Columns line of code. Also, it appears that your in statement has two resulting lines which should only have one since I see only 1 let statement. Your changed type is referencing your source line of code. Is this correct? You also have two changed type rows with the same name. That should be causing an error also.
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Fluff said:
Try changing the u (everywhere it occurs) to something else, maybe Data
Click to expand...
I replaced all u with Data and the , with ; and i get this:
Excel Formula: 
=LET(Data;UNIQUE(FILTER(CHOOSECOLS(Table4[[Code]:[Group]];1;2;-1);Table4[Type]="Overcode"));HSTACK(TAKE(Data;;2);SUMIFS(Table4[Amount];Table4[Code];INDEX(Data;;1));COUNTIFS(Table4[Code];INDEX(Data;;1));TAKE(Data;;-1)))
1725355097137.png
 
Upvote 0
alansidman said:
Looks like you are missing a comma at the end of the Renamed Columns line of code. Also, it appears that your in statement has two resulting lines which should only have one since I see only 1 let statement. Your changed type is referencing your source line of code. Is this correct? You also have two changed type rows with the same name. That should be causing an error also.
Click to expand...
Honestly, I have no clue about anything, this power query code is something someone else gave me on this forum, i dont understand it at all. To avoid confusion, isn't it better to make a new power query or something to make my 2nd table?
 
Upvote 0
Honestly, I have no clue about anything, this power query code is something someone else gave me on this forum, i dont understand it at all. To avoid confusion, isn't it better to make a new power query or something to make my 2nd table?
Click to expand...
Perhaps. If you have no knowledge and understanding of PQ and wish to pursue it, then I suggest you investigate and find some tutorials instead of trusting others to fix problems you don't understand. I suggest you start with Ken Puls and Miguel Escobar book on Power Query. It can be found on MrE book listing on this forum or on Amazon
 
Upvote 0
Fluff said:
Check that the column names are correct, especially check for any leading/trailing spaces.
Click to expand...
=LET(Data;UNIQUE(FILTER(CHOOSECOLS(C:H;1;2;-1);G:G="Overcode"));HSTACK(TAKE(Data;;2);SUMIFS(E:E;C:C;INDEX(Data;;1));COUNTIFS(C:C;INDEX(Data;;1));TAKE(Data;;-1)))
I made it work using references to the columns instead of the Table, I even went in powerquery to double check all the names didnt work at all. But using column references does. Thank you ur formula works.

Small question, with this I can't sort from highest to lowest, I get this:
1725394015614.png
is this fixable or something I have to live with?
 
Upvote 0
You would need to use to use the SORT function, as you cannot use the sort option on the Data tab.
 
Upvote 0
Fluff said:
You would need to use to use the SORT function, as you cannot use the sort option on the Data tab.
Click to expand...
Hey I have a quick question regarding this,
I implemented the sort function and it works, but now how I understood and did it makes me another table, is it possible to directly implement it into ur formula?
1725481002860.png


and it makes me a million rows of 0's
1725481132217.png
at the bottom, is this also avoidable?
I read somewhere that using =IF(SORT(J:N;4;-1;FALSE)="";"";SORT(J:N;4;-1;FALSE)) removes all the 0's and it does, but i still get a million rows :l
 
Last edited:
Upvote 0
You can sort it like
Excel Formula: 
=LET(u,UNIQUE(FILTER(CHOOSECOLS(Table4[[Code]:[Group]],1,2,-1),Table4[Type]="overcode")),SORT(HSTACK(TAKE(u,,2),SUMIFS(Table4[Amount],Table4[Code],INDEX(u,,1)),COUNTIFS(Table4[Code],INDEX(u,,1)),TAKE(u,,-1)),4,-1))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

Ramballah
  • Question Question
Transferring raw data into my own sheet
Replies
7
Views
277
Kerryx
K

Forum statistics

Threads
1,223,885
Messages
6,175,183
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