sumproduct function with text??

[Guest]

Here's my data:

A B C D E
F G
George Jones Pro Deb Smith Am 153
Jack Thomas Pro Wendy Jones Am 154


Columns A & B are names of the pro dance partner.....D & E are amateur
partner....G is the comp number.

Need the output to read as follows in a report:

George Jones (P) and Deb Smith (A) #153
Jack Thomas (P) and Wendy Jones (A) #154


Thought I could use SUMPRODUCT, but can't get it to work....

Any thoughts? Thanks so much in advance!

 

[JE McGimpsey]

You've given the output, but what would be the input(s)?

Do you want the output in one cell, or more?


You might be able to use sumproduct:

J K L M
1 George Jones Deb Smith

=J1 & " " & K1 & " (P) and " & L1 & " " & M1 & " (A1) #" &
SUMPRODUCT(--(A1:A1000=J1), --(B1:B1000=K1), --(D11000=L1),
--(E11000=M1), G1:G1000)

 

[Guest]

Julz

You could use the Concatenate function or just:

=A2&" "&B2&" "&"("&LEFT(C2)&") "&D2&" "&E2&" ("&LEFT(F2)&") #"&G2

SUMPRODUCT is for counting how many rows refer to Deb and Jack or how many
Pro(s) there are.

Regards
Peter

 

[Guest]

Yes, I would like the input to be consolidated into 1 column for the output.

So, Columns A - G on worksheet #1 would end up on a in Column A on worksheet
#2.

Worksheet #1 Input:

A B C D E F G
1 George Jones Pro Deb Smith Am 153
2 Jack Thomas Pro Wendy Jones Am 154



Worksheet #2 Output:
A
1 George Jones (P) and Deb Smith (A) #153
2 Jack Thomas (P) and Wendy Jones (A) #154


Hope this clarifies it....

 

[Pete_UK]

Assuming your data starts on row 1, and that you need the report on a
separate sheet, then Insert | Worksheet and in that new sheet cell A1
enter this formula:

=Sheet1!A1&" "&Sheet1!B1&" (P) and "&Sheet1!D1&" "&Sheet1!E1&" (A)
#"&TEXT(Sheet1!G1,"000")

(all one formula - be wary of spurious line-breaks in the newsgroups).
Copy this down column A for as many entries as you have in Sheet1.

Hope this helps.

Pete

 

[Rick Rothstein \(MVP - VB\)]

Assuming your Worksheet #1 is named Sheet1, I would think this formula would
do what you want...

=Sheet1!A1&" "&Sheet1!B1&" (P) and "&Sheet1!D1&" "&Sheet1!E1&" (A)
#"&Sheet1!G1

Rick

 

[JE McGimpsey]

Then just use

Sheet2!A1:

=Sheet1!A1 & " " & Sheet1!B1 7 " (P) and " & Sheet1!D1 & " " &
Sheet1!E1 & " (A) #" & Sheet1!G1


Copy down as far as needed.

 

[Guest]

WORKED GREAT!! THANKS!

Assuming your data starts on row 1, and that you need the report on a
separate sheet, then Insert | Worksheet and in that new sheet cell A1
enter this formula:

=Sheet1!A1&" "&Sheet1!B1&" (P) and "&Sheet1!D1&" "&Sheet1!E1&" (A)
#"&TEXT(Sheet1!G1,"000")

(all one formula - be wary of spurious line-breaks in the newsgroups).
Copy this down column A for as many entries as you have in Sheet1.

Hope this helps.

Pete

 

[Guest]

worked like a charm!! THANK YOU!!

Assuming your Worksheet #1 is named Sheet1, I would think this formula would
do what you want...

=Sheet1!A1&" "&Sheet1!B1&" (P) and "&Sheet1!D1&" "&Sheet1!E1&" (A)
#"&Sheet1!G1

Rick

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

WORKED GREAT!! THANKS!








- Show quoted text -