-3

I want to make a macro in Excel that summarizes data from rows that match a composite ID generated from 2 ID columns. In my excel sheet, each row has 2 main ID columns: ID_1 is the main key, and ID_2 is a secondary key from which I only care about the first 2 letters (Which I have gotten using LEFT). I want to group rows with the same ID_1 and first 2 letters of ID_2 and report the SUM of the value, count, and sum columns.

In the example picture below, I want to turn the data in columns A:J into the data in columns M:V

So, with this example -> We have 6 records 1015 (ID_1) with 3 different ID_2 (AB, AZ, AE). I want to sum them up to a one cell each (1015 AB ; 1015 AZ ;1015 AE) with values which each record had (there is 3 records: 1015 AB with VALUE of 2,3,4 so in result I want to get just one row 1015 AB 9(sum of value) 4(sum of count) 17 (sum of(value * count)). It's important to see that this 17 dosn't come from 9 * 4. It's =sum(I4:I6) (but it may be spread out like in 1200 FF example below! I am still trying to sort them both at one time, but I cant get past it..)

enter image description here

divibisan
  • 11,659
  • 11
  • 40
  • 58
K. Rav
  • 11
  • 1
  • 2
    To make **what** faster? I don't see anything to make 'faster'. –  Aug 07 '18 at 20:06
  • As you can see, images on the Web aren't guaranteed to load (nice useless alt text btw). **Paste** the *actual code* in the post, select it, then hit Ctrl+K to automatically add the 4-space indent to all lines, making it format as code. If that image wasn't your code, ...[edit] your post to include the code anyway. – Mathieu Guindon Aug 07 '18 at 20:40

1 Answers1

0

Add a helper column in D to combine the ID_1 and the first 2 characters of ID_2. =A4 & LEFT(C4,2). Copy that down then go to L4 and type in:

=+INDEX($D$4:$D$25,MATCH(0,COUNTIF(L$3:L3,$D$4:$D$25),0)

and hold down Ctrl + Shift + Enter to make it an array function. Copy down to get a list of unique combinations, and then split these values into the separate columns.

Finally to pull in the numbers, put this in Q4:

=SUMIFS(E$4:E$25,$A$4:$A$25,M4,$C$4:$C$25,O4 & "*")

and then copy down and across.

Chris Walker
  • 144
  • 6
  • It looks nice, I did think about it in this way, but main problem was that how to make a columns in M and O with data which you see in example. Macro or formula which will see that there is in column C three different ID_2 with same ID_1 from column A and "stack" them together elsewhere in sheet. – K. Rav Aug 08 '18 at 10:50
  • Oh. How often do these values change? Would there be different combinations of ID_1 and ID_2 every time the sheet is updated, or would these be pretty stable and only change occasionally? If they change pretty often, then I probably would use a macro to collect all the ID_1-ID_2-prefix combinations as keys in a Scripting Dictionary object, and an array of the numbers as a value such as in [this solution](https://stackoverflow.com/questions/17656580/create-dictionary-of-lists-in-vba) – Chris Walker Aug 08 '18 at 15:33
  • There will be new combinations every time there is a new Excel. I will check your suggestion. :) – K. Rav Aug 09 '18 at 08:35
  • And also there will be a different number of records with every Excel. On one case there can be 100 records, on next one there can be 10000 records. – K. Rav Aug 09 '18 at 09:21
  • I guess that I found pretty good answer. I will sum up ID_1 and ID_2 in column L, then I will check something like "first occurrence" of every record in this column, then I will split them again in ID_1 and ID_2 from columns M and O. – K. Rav Aug 09 '18 at 15:24
  • Yes! That's a great idea. And you can use an array function to extract the unique combinations. If you put the combined ID_1 and ID_2 in column K, you can go to L4 and type in +INDEX($A$4:$A$25,MATCH(0,COUNTIF(L$3:L3,$A$4:$A$25),0)) and hold down Ctrl+Shift+Enter to make it an array formula. Then copy the cell down and it will give you a list of unique values. – Chris Walker Aug 09 '18 at 18:36