sumproduct function with text??

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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), --(D1:D1000=L1),
--(E1:D1000=M1), G1:G1000)
 
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
 
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....
 
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
 
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
 
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.
 
WORKED GREAT!! THANKS!

Pete_UK said:
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
Click to expand...
 
worked like a charm!! THANK YOU!!

Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
Back
Top