Aligning Two Lists in Excel

[Rich]

I hope someone can help. I would like to take two long lists of data
records in excel, and align them wherever they match.

The problem I have is that the lists are not 'flat' (although I maybe able
to overcome this), and items in the first list may not necessarily appear in
the second or the other way around, but I would like the final list to be a
combination of the two.

To illustrate;

List(1)
----A-----|----B----|----C----|----D----|
Apple Tree|---------|---------|-----1000|
----------|Apple----|---------|------700|
----------|---------|Red------|------600|
----------|---------|Green----|------100|
Pear Tree-|---------|---------|-----1000|
----------|Pear-----|---------|------700|
----------|---------|Red------|------600|
----------|---------|Green----|------100|
Peach Tree|---------|---------|-----1500|
----------|Peach----|---------|-----1500|
----------|---------|Orange---|------700|
----------|---------|White----|------800|

...etc

List(2)
----A--------|----B----|----C----|----D----|
Apple Tree---|---------|---------|------350|
-------------|Apple----|---------|------350|
-------------|---------|Green----|------350|
Banana Plant-|---------|---------|-----1000|
-------------|Bananas--|---------|------600|
-------------|---------|Yellow---|------600|
Peach Tree---|---------|---------|-----1000|
-------------|Peach----|---------|------700|
-------------|---------|Orange---|------600|
-------------|---------|White----|------100|

Combined List

----A--------|----B----|----C----|----D----|----E----|
Apple Tree---|---------|---------|------350|-----1000|
-------------|Apple----|---------|------350|------700|
-------------|---------|Red------|---------|------600|
-------------|---------|Green----|------350|------100|
Pear Tree----|---------|---------|---------|-----1000|
-------------|Pear-----|---------|---------|------700|
-------------|---------|Red------|---------|------600|
-------------|---------|Green----|---------|------100|
Banana Plant-|---------|---------|-----1000|---------|
-------------|Bananas--|---------|------600|---------|
-------------|---------|Yellow---|------600|---------|
Peach Tree---|---------|---------|-----1000|-----1500|
-------------|Peach----|---------|------700|-----1500|
-------------|---------|Orange---|------600|------700|
-------------|---------|White----|------100|------800|

Many thanks in advance..

 

[Dave O]

Hi, Rich-
First, let me say "Nice job" in detailing the column layout in your
original post. These often get mangled when they get posted, but yours
survived intact and made determining the answer very easy.

This is a fairly involved process, because you need to create a unique
identifier for each line on the sprdsht. However, you can't use the
entry on each line because "Red" can apply to both apples and pears.
The solution is to create a unique entry for each line by concatenating
"Red" with "Apple" and "Apple Tree". This will distinguish apple tree
"Red" from pear tree "Red".

Here's how I did it:
1. Make a copy of the original sprdsht so you don't lose irrecoverable
data. Work out all these details on the copied data until you are 100%
confident you won't lose data.
2. At the top of both List1 and List2, enter a single blank line.
(This is to accommodate a formula that returns a blank entry under
certain circumstances.)
3. I assumed List1 and List2 appear in separate tabs called List1 and
List2, and that your original data appears in columns A - D. I assumed
your original data appeared in Row 1 which is now Row 2 because of
instruction 2 above. Leave column E blank; in cell F2 enter this
formula:
=IF($D2>0,A2&F1,"")
This applies to both List1 and List2.
4. Copy this formula across to column G and down for as many rows as
necessary.
5. In cell H2 enter this formula, and copy down for all rows:
=IF(AND(C2<>"",D2>0),C2,"")
6. In cell I2 enter this formula:
=IF(D2>0,F2&G2&H2,"")
This creates the unique identifier for each row. Copy it down for all
rows.
7. In both List1 and List2, cell J2, enter this formula:
=IF(A2<>"",A2,"")
Copy this formula over to cell M and down for all rows. This
reproduces existing data, but greases the skids for a lookup function
later.
8. Copy all entries in List1 column I, and paste them into a new tab
called "Summary" Column A. Copy all entries from List 2 Column I and
paste them into the Summary tab in column A *below* the list 1 entries.
9. Highlight Summary tab column A, and sort ascending.
10. This part can be tricky and unpleasant: remove any blank rows that
appear above your data, and remove any duplicate entries that appear in
Column A. The result is a list of unique identifiers from both tabs;
each unique identifier is represented only once. Remove any blank rows
at the top such that your data appears in cell Summary!A1.
11. In the Summary tab, insert two rows at the top of your data, such
that data now starts in cell Summary!A3. Enter the following values in
these cells:
B2: 2
C2: 3
D2: 4
E2: 5
F2: 5
Create headers in row 1, something along the lines of
B1: Plant
C1: Variety
D1: Color
E1: List1 price
F1: List2 price
12. In Summary!B3 enter this formula:
=IF(ISERROR(VLOOKUP($A3,List1!$I$2:$M$21,B$2,0)),VLOOKUP($A3,List2!$I$2:$M$21,B$2,0),VLOOKUP($A3,List1!$I$2:$M$21,B$2,0))
This formula returns a value from List1 if there is a value to be
found, or looks in List2 if the value is not in List1. Copy this
formula across to cell D3 and down for all rows.
13. In Summary!E3 enter this formula:
=IF(ISERROR(VLOOKUP($A3,List1!$I$2:$M$21,E$2,0)),"",VLOOKUP($A3,List1!$I$2:$M$21,E$2,0))
This formula returns a value from List1 if it can be found, or else
leaves the entry blank.
14. In Summary!F3 enter this formula:
=IF(ISERROR(VLOOKUP($A3,List2!$I$2:$M$21,F$2,0)),"",VLOOKUP($A3,List2!$I$2:$M$21,F$2,0))
This formula returns a value from List2 if it can be found, or else
leaves the entry blank.

NOTE! Read the Help! entry for the VLOOKUP function if you're not
already familiar with it, and adjust the VLOOKUP formulas to match the
arrays in List1 and List2.

And that's it, altho you may want to make some cosmetic changes such as
hiding Summary!Row 2 and Summary!Column A, and inserting separator rows
between plant types.

Contact me, (e-mail address removed), with a valid email address and I'll
send you my workup Excel file.

Dave O

 

[Earl Kiosterud]

Dave, Rich,

I agree that it was a nice job on Rich's part getting your table posted and
having it come out intact. I can't tell you how many posts I've abandoned
in frustration because the posted table got so convoluted. For general
edification, here are some tips for folks for posting a table (row/column
stuff):

1. Use a non-proportional font when you compose your post that contains a
table (Courier is good). That way, every character has the same horizontal
spacing, and stuff lines up vertically in the various lines of the post.
Sending in plain text (as opposed to html) is the preferred method for
newsgroups, though in that case it's important that any reader of a post
also use a non-proportional font for displaying the post, so the spacing
will be maintained. I suppose sending in html would force the use of the
non-proportional font at the reader's machine too. I guess all newsreaders
can handle html by now. Anyone have any thoughts on the best netiquette?

In Outlook Express, controlling the font used for composing a post is done
with Tools - Options - Compose, and for reading a post, with Tools -
Options - Read.

2. Keep the lines under 76 characters. Line wrapping messes up the table
readily. You won't see the wrapping as it will be sent as you compose, so
you may not realize the lines will wrap. A maximized window will hold 132
characters of Courier on a line, but normally Outlook Express is set to wrap
(actually put a linefeed in the line when the post is sent) after 76 (or
some such) characters. It's a bit primitive.