I'm currently working on a list of products with a variety of
categories for each, which I have in Excel (and will end up in Word).
I need to know if it's possible (and the Excel formula for making it
happen) to compare two fields in a row: If they're different, they
should stay as is, but if they're the same, I'd like them combined.
An example of this particular data in a row would be:
Author:<tab>author name<tab>Editor:<tab>editor name
If the author and editor are different names, it'll end up looking
like this in Word:
Author: John Doe
Editor: Joe Blow
But if they're the same, they'll be combined into:
Author/Editor: John Doe.
Can this be done using Excel formulas?
My preference would be to have the "Author: name" and "Editor: name"
end up as two fields, but I'm aware for the comparison, that process
will need four fields. So that I can keep the list in Excel with
everything lining up under the column heads, the end result of the
formula should either be:
Author: name<tab>Editor: name
or
Author/Editor: name<tab><blank field>
(I can take care of the process moving the data from Word to Excel
with the whole list, turn the two Author and Editor columns into the
four needed for comparison, then back to my preferred Word format
afterward; I just need the formula I can plug in to do the comparison
on each row.)
A nice tip will be provided for a speedy working solution. And, no
offense, but I don't need an explanation of how it works...just that
it works for my purposes, because I'm on a relatively short deadline.
Thanks in advance...
A E |
Request for Question Clarification by
tar_heel_v-ga
on
07 Oct 2004 15:14 PDT
Can the names be in separate columns, ie:
Author Last Author First Editor First Editor Last
Smith James Jones Frank
Allen Sam Allen Sam
|
Request for Question Clarification by
tar_heel_v-ga
on
07 Oct 2004 15:27 PDT
Let's give this a shot and let me know if this will meet your needs:
Row 1 Column A Author Name
Row 1 Column B Editor Name
Row 2 Column C =IF(A2=B2,"Editor/Author","Author")
Row 2 Column D =IF(C2="Editor/Author",B2,A2)
Row 2 Column E Empty
Row 2 Column F =IF(C2="author","Editor","")
Row 2 Column G =IF(C2="Editor/Author"," ",B2)
In row 2 and succeeding rows, column A will be the Author Name, Column
B will be the Editor Name
Copy the information in columns C-G all the way down the rows.
This will give you the following results:
If name is the same in A and B, the result will be "Editor/Author Name"
If name is different in A and B, the result will be "Author Name
<column> Editor Name"
If this will work for your format, let me know and I can "pretty it
up" so that the columns will be empty when there is no data, etc.
-THV
|
Clarification of Question by
vidgames-ga
on
07 Oct 2004 16:44 PDT
It didn't work perfectly, but I took what you offered and tailored it
(because it helped me understand what you were aiming for), and now it
works. I'm not actually using authors and editors, but rather company
names, so your first question of separating first and last name
wouldn't have worked.
Just for your purposes, this is what I have in my conversion sheet,
using the same author/editor example:
A1="Author:"
B1=<author name data>
C1="Editor:"
D1=<editor name data>
E1=IF(B1=D1,"Author/Editor:","Author:")
F1==IF(E1="Author/Editor",D1,B1)
G1=<empty>
H1=IF(B1=D1,"","Editor:")
I1=IF(E1="Author/Editor:","",D1)
Though I haven't tried it yet, my guess is I can make F1 just equal to
B1, because there's really not a comparison-and-change needed--it'll
always stay what it was. I also figure the G1 blank column can be
removed (it's just an extra tab that I'll have to find/replace out of
the final doc back in Word). And I took out the space in the quotes in
cell I1 because that just would have added an unnecessary space that
would have to be edited out of the final document.
Thanks much for 99.9% of the work. (hehe) I'm not sure what you have
to do next to get paid, but I'm happy with the results.
|
