Home 
Search 
Today's Posts 
#1




Aligning Two Lists in Excel
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) ABCD Apple Tree1000 Apple700 Red600 Green100 Pear Tree1000 Pear700 Red600 Green100 Peach Tree1500 Peach1500 Orange700 White800 ...etc List(2) ABCD Apple Tree350 Apple350 Green350 Banana Plant1000 Bananas600 Yellow600 Peach Tree1000 Peach700 Orange600 White100 Combined List ABCDE Apple Tree3501000 Apple350700 Red600 Green350100 Pear Tree1000 Pear700 Red600 Green100 Banana Plant1000 Bananas600 Yellow600 Peach Tree10001500 Peach7001500 Orange600700 White100800 Many thanks in advance.. 
#2




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($D20,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<"",D20),C2,"") 6. In cell I2 enter this formula: =IF(D20,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)),V LOOKUP($A3,List2!$I$2:$M$21,B$2,0),VLOOKUP($A3,Lis t1!$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, , with a valid email address and I'll send you my workup Excel file. Dave O 
#3




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 nonproportional 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 nonproportional font for displaying the post, so the spacing will be maintained. I suppose sending in html would force the use of the nonproportional 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.  Earl Kiosterud mvpearl omitthisword at verizon period net  "Dave O" wrote in message ps.com... 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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Excel converts Mapped drive to UNC  How to stop?  Links and Linking in Excel  
Creating GIFs in Excel 2003 for use in Dreamweaver  Charts and Charting in Excel  
Charting Data from Excel  Charts and Charting in Excel  
Mexport transfer from another program to excel opens at excel lau.  Excel Discussion (Misc queries)  
Staring Excel Problem  Excel Discussion (Misc queries) 