Multiple rows to single row by criteria

Max Endoso · Oct 20, 2003

Greetings everyone. I hope you can help me with this
problem. When I get my data from a mainframe query report
it looks like this:
Person Charge Amount
A 1 100.00
A 2 100.00
B 1 100.00
B 2 100.00

I need to format my report like this:
Person Charge Total Amount
A 1,2 200.00
B 1,2 200.00

Thank you in advance for your help.

Dan E · Oct 21, 2003

Max,

Getting the sum is no problem using a sumif or a sumproduct,
the problem is getting the 1,2...

Assuming your data is in A1:C5 and the summary in E1:G3 the
sum is
=SUMIF(A2:A5,E2,C2:C5)
OR
=SUMPRODUCT((A2:A5=E2)*(C2:C5))

To get the Charges I made a UDF,

Function ArrayIt(InArray As Variant) As String
For Each ArrayVal In InArray
If ArrayVal <> "" And ArrayVal <> 0 Then ArrayIt = ArrayIt & "," & ArrayVal
Next
ArrayIt = Right(ArrayIt, Len(ArrayIt) - 1)
End Function

Press [ Alt + F11 ] to open the VBA Window
Locate your workbook/project (ie VBAProject(your_book_name_here))
Right click and choose: Insert -> module, a code window should appear (named
your_book_name_here - Module 1 (code)
Past in the above code and close the VBA window
MORE ON MACROS/UDF's ( http://www.mvps.org/dmcritchie/excel/getstarted.htm )

Function to get the "1,2":
use the UDF like
=ArrayIt((A2:A5=E2)*(B2:B5))
array entered (control + shift + enter) (entered properly {} should appear around
the formula)

drag down the formulas for all your persons

Dan E

Max Endoso · Oct 21, 2003

Thanks for your response. I followed everything that you
said but I'm getting a "#VALUE!" error after I entered the
array formula. Do you know the cause of this?

-----Original Message-----
Max,

Getting the sum is no problem using a sumif or a sumproduct,
the problem is getting the 1,2...

Assuming your data is in A1:C5 and the summary in E1:G3 the
sum is
=SUMIF(A2:A5,E2,C2:C5)
OR
=SUMPRODUCT((A2:A5=E2)*(C2:C5))

To get the Charges I made a UDF,

Function ArrayIt(InArray As Variant) As String
For Each ArrayVal In InArray
If ArrayVal <> "" And ArrayVal <> 0 Then ArrayIt = ArrayIt & "," & ArrayVal
Next
ArrayIt = Right(ArrayIt, Len(ArrayIt) - 1)
End Function

Press [ Alt + F11 ] to open the VBA Window
Locate your workbook/project (ie VBAProject (your_book_name_here))
Right click and choose: Insert -> module, a code window should appear (named
your_book_name_here - Module 1 (code)
Past in the above code and close the VBA window
MORE ON MACROS/UDF's (

http://www.mvps.org/dmcritchie/excel/getstarted.htm )

Function to get the "1,2":
use the UDF like
=ArrayIt((A2:A5=E2)*(B2:B5))
array entered (control + shift + enter) (entered properly {} should appear around
the formula)

drag down the formulas for all your persons

Dan E

"Max Endoso" <[email protected]> wrote

in message news:[email protected]...

Dan E · Oct 21, 2003

Max,

The only reason that I can think is the array entering
(control + shift + enter). Does you formula look like
{=ArrayIt((A2:A5=E2)*(B2:B5))} when you select the
cell with the formula in it? (Note the {} braces around
the formula.

Dan E

Max Endoso said:
Thanks for your response. I followed everything that you
said but I'm getting a "#VALUE!" error after I entered the
array formula. Do you know the cause of this?

-----Original Message-----
Max,

Getting the sum is no problem using a sumif or a sumproduct,
the problem is getting the 1,2...

Assuming your data is in A1:C5 and the summary in E1:G3 the
sum is
=SUMIF(A2:A5,E2,C2:C5)
OR
=SUMPRODUCT((A2:A5=E2)*(C2:C5))

To get the Charges I made a UDF,

Function ArrayIt(InArray As Variant) As String
For Each ArrayVal In InArray
If ArrayVal <> "" And ArrayVal <> 0 Then ArrayIt = ArrayIt & "," & ArrayVal
Next
ArrayIt = Right(ArrayIt, Len(ArrayIt) - 1)
End Function

Press [ Alt + F11 ] to open the VBA Window
Locate your workbook/project (ie VBAProject (your_book_name_here))
Right click and choose: Insert -> module, a code window should appear (named
your_book_name_here - Module 1 (code)
Past in the above code and close the VBA window
MORE ON MACROS/UDF's (

Click to expand...

http://www.mvps.org/dmcritchie/excel/getstarted.htm )

Function to get the "1,2":
use the UDF like
=ArrayIt((A2:A5=E2)*(B2:B5))
array entered (control + shift + enter) (entered properly {} should appear around
the formula)

drag down the formulas for all your persons

Dan E

"Max Endoso" <[email protected]> wrote

Click to expand...

in message news:[email protected]...

.

Click to expand...

Dan E · Oct 21, 2003

Max,

If it's not the array entering, post the formula as you have
it in your sheet... It could be bad range sizes.

Dan E

Max Endoso said:
Thanks for your response. I followed everything that you
said but I'm getting a "#VALUE!" error after I entered the
array formula. Do you know the cause of this?

-----Original Message-----
Max,

Getting the sum is no problem using a sumif or a sumproduct,
the problem is getting the 1,2...

Assuming your data is in A1:C5 and the summary in E1:G3 the
sum is
=SUMIF(A2:A5,E2,C2:C5)
OR
=SUMPRODUCT((A2:A5=E2)*(C2:C5))

To get the Charges I made a UDF,

Function ArrayIt(InArray As Variant) As String
For Each ArrayVal In InArray
If ArrayVal <> "" And ArrayVal <> 0 Then ArrayIt = ArrayIt & "," & ArrayVal
Next
ArrayIt = Right(ArrayIt, Len(ArrayIt) - 1)
End Function

Press [ Alt + F11 ] to open the VBA Window
Locate your workbook/project (ie VBAProject (your_book_name_here))
Right click and choose: Insert -> module, a code window should appear (named
your_book_name_here - Module 1 (code)
Past in the above code and close the VBA window
MORE ON MACROS/UDF's (

Click to expand...

http://www.mvps.org/dmcritchie/excel/getstarted.htm )

Function to get the "1,2":
use the UDF like
=ArrayIt((A2:A5=E2)*(B2:B5))
array entered (control + shift + enter) (entered properly {} should appear around
the formula)

drag down the formulas for all your persons

Dan E

"Max Endoso" <[email protected]> wrote

Click to expand...

in message news:[email protected]...

.

Click to expand...

Max Endoso · Oct 21, 2003

It's not the array entering because I have it exactly the
same as you showed me. Here's my formula:
{=ArrayIt((B8:B25=E8)*(C8:C25))}

-----Original Message-----
Max,

If it's not the array entering, post the formula as you have
it in your sheet... It could be bad range sizes.

Dan E

"Max Endoso" <[email protected]> wrote

in message news:[email protected]...

Thanks for your response. I followed everything that you
said but I'm getting a "#VALUE!" error after I entered the
array formula. Do you know the cause of this?

-----Original Message-----
Max,

Getting the sum is no problem using a sumif or a sumproduct,
the problem is getting the 1,2...

Assuming your data is in A1:C5 and the summary in E1:G3 the
sum is
=SUMIF(A2:A5,E2,C2:C5)
OR
=SUMPRODUCT((A2:A5=E2)*(C2:C5))

To get the Charges I made a UDF,

Function ArrayIt(InArray As Variant) As String
For Each ArrayVal In InArray
If ArrayVal <> "" And ArrayVal <> 0 Then

Click to expand...

ArrayIt
= ArrayIt & "," & ArrayVal

Next
ArrayIt = Right(ArrayIt, Len(ArrayIt) - 1)
End Function

Press [ Alt + F11 ] to open the VBA Window
Locate your workbook/project (ie VBAProject (your_book_name_here))
Right click and choose: Insert -> module, a code window should appear (named
your_book_name_here - Module 1 (code)
Past in the above code and close the VBA window
MORE ON MACROS/UDF's (

Click to expand...

http://www.mvps.org/dmcritchie/excel/getstarted.htm )

Function to get the "1,2":
use the UDF like
=ArrayIt((A2:A5=E2)*(B2:B5))
array entered (control + shift + enter) (entered

Click to expand...

properly
{} should appear around

the formula)

drag down the formulas for all your persons

Dan E

"Max Endoso" <[email protected]>

Click to expand...

wrote
in message news:031301c39750$d8db89b0 [email protected]...

Greetings everyone. I hope you can help me with this
problem. When I get my data from a mainframe query report
it looks like this:
Person Charge Amount
A 1 100.00
A 2 100.00
B 1 100.00
B 2 100.00

I need to format my report like this:
Person Charge Total Amount
A 1,2 200.00
B 1,2 200.00

Thank you in advance for your help.

.

Click to expand...

Click to expand...

.

Dan E · Oct 21, 2003

Send me your sheet, I'll see if I can find the problem...

Max · Oct 23, 2003

I tried twice sending it to (e-mail address removed) and
both came back undeliverable. Do you have another email
address? Thanks.

-----Original Message-----
Send me your sheet, I'll see if I can find the problem...

--
Dan E

(e-mail address removed)
remove the "nospam."s and put excel in the subject line

"Max Endoso" <[email protected]> wrote

in message news:[email protected]...

Max Endoso · Oct 30, 2003

Dan,
thanks for hanging in there. Here's my email
(e-mail address removed)

-----Original Message-----
Max,

If you post one (an address) i'll send it to you...

Dan E

"Max" <[email protected]> wrote in

message news:[email protected]...