Index/Match Formula

Anthony,

Care to post 10 rows of your (cooked up if needed) data along with what kind of summing/totaling you need?

Aladin

Thanks for the response Aladin! Here it goes.

SP186412a 500 10,000
SP186412c 500 10,000
SP186412r 500 10,000

SP186413a 400 20,000
SP186413c 400 20,000
SP186413r 400 20,000

SP186414a 300 5,000
SP186414c 300 5,000
SP186414r 300 5,000

This is how I receive the data. I would like to "dump" it in one worksheet and have a formula in another that will allow me to obtain the following results:

SP186412 1,500 30,000
SP186413 1,200 60,000
Sp186414 900 15,000

The formula that I originally had provided will allow me to obtain the desire results for only one number. As you can see, I would like to consolidate numerous lines into one.

Thanks Again

Anthony

Consolidation by means of array formulas

Hi Anthony,

My first caveat: I'm using in what follows a system of (array) formulas to obtain the desired results. If someone comes up with a proposal that avoids using array formulas, you should consider switching to that solution.

That being said, here we go.

I'll assume your sample data to be in A1:D10 which look as:

{"Codes","Ucodes","Values1","Values2";"SP186412a","SP186412",500,10000;"SP186412c","",500,10000;"SP186412r","",500,10000;"SP186413a","SP186413",400,20000;"SP186413c","",400,20000;"SP186413r","",400,20000;"SP186414a","SP186414",300,5000;"SP186414c","",300,5000;"SP186414r","",300,5000}

As you can see I introduced some labels and inserted an additional column with the label Ucodes (for Unique Codes).

In B2 enter: =IF(ISNUMBER(MATCH(LEFT(A2,LEN(A2)-1),$B$1:B1,0)),"",LEFT(A2,LEN(A2)-1)) [ copy this down up to the last row of Codes ]

In F2 enter: # of records [ just a label ]
In G2 enter: =COUNTIF(A:A,"SP*") [ This formulas assumes that the codes you receive all begin with "SP". ]

In F3 enter: # of Unique Codes [ just a label ]
In G3 enter: =COUNTIF(B:B,"SP*") [ The assumption of G2 also holds here. ]

In F4 enter: List1 [ just a label ]
In G4 enter: =ADDRESS(ROW(2:2),COLUMN(B:B))&":"&ADDRESS(G2,COLUMN(B:B))

In F5 enter: List2 [ just a label ]
In G5 enter: =ADDRESS(ROW(2:2),COLUMN(I:I))&":"&ADDRESS(G2,COLUMN(I:I))

In I2 array-enter: =IF(ROW()-ROW(INDIRECT($G$5))+1>ROWS(INDIRECT($G$4))-COUNTIF(INDIRECT($G$4),""),"",INDIRECT(ADDRESS(SMALL((IF(INDIRECT($G$4)<>"",ROW(INDIRECT($G$4)),ROW()+ROWS(INDIRECT($G$4)))),ROW()-ROW(INDIRECT($G$5))+1),COLUMN(INDIRECT($G$4))))) [ You need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) to enter an array formula. ]

Copy this array formula as far as desired/needed.

In J2 array-enter: =IF(LEN(I2)>=1,SUM((LEFT($A$2:$A$10,LEN($A$2:$A$10)-1)=$I2)*(C$2:C$10)),"")

Copy this array formula first to K2, then down.

You'll see the following in F2:G5.

{"# of records",9;"# of Unique Codes",3;"List1","$B$2:$B$9";"List2","$I$2:$I$9"}

And, finally you'll see the desired results in I2:K4.

{"SP186412",1500,30000;"SP186413",1200,60000;"SP186414",900,15000}

Note. The additional column can also be inserted before or after your original range with the needed adjustments to the formulas.

Aladin

PS. If you'd rather have a copy of the workbook containing the above system, let me know.

===================

Re: Consolidation by means of array formulas

Aladin, you are my newly proclaimed hero! A copy of the workbook would be great. Thanks Again! I owe you one! Oh, just one more question one this matter. If I only wanted to sum let's say any account number ending with just an "a" or an "r", yet still have the entire budget column (last column) sum up, can I still do it with this formula? Talk to you soon!!!

Anthony

Re: Consolidation by means of array formulas

Yes, with a small modification. The array formula that will do it is:

=SUM((RIGHT(A2:A10,1)={"a","r"})*(D2:D10))

Aladin