Excel Magic Trick 369: Compare 2 Lists, Extract Records Not Listed Twice

So I used Excel Magic Trick 185 to dynamically pull content from a spreadsheet: http://www.youtube.com/watch?v=6bGKhbUYOas

However,
It is only working for a few of my columns though and I am getting reference errors, http://www.mediafire.com/?gje8srbp8x2j48w

Can anyone check into my spreadsheet and help me understand why I get these ref errors? Is it because I have spaces in the column names or within the cells? I know a macro is probably a better way to do this, or an auto-filter, but I need it to dynamically update. Any ideas on how to fix this problem or a better way to solve it?

Thanks!

I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:

Code:

=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])

except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

Hello there.
First, and before all, I must admit that I am a kind a "dumb" for Excel, because I always were using it, only for better look's of simple tables, schedule's and similar. Although, I was all the time aware of great power's of this program, but newer tested it.
So, finally after few years of tempting, I decided to make a proper "order-list" for the shop in which I work. What I needed is to ,based on products and their prices, make a drop-down box, with list of the products, and after choosing one , proper price will be shown in next column. I find out how to do it with "Excel magic trick #5" on Youtube.
This is what i achieved:

http://i49.tinypic.com/dorzgn.jpg

However,
You see that in row 16, or line 2 in the order box ,we have #N/A below Cena (Price), Iznos PDV (amount of VAT), Cena sa PDV (Amount with VAT), and Iznos (Total amount). If we choose one of the product from drop-down box (now, as You can see none is chosen) and type number in Kolicina(Quantity) column, all this would be automatically changed into proper value.
But what if we have only one thing in order box, instead of 2 or all of 10 ? How to avoid those #N/A, and make instead to be blank cell, which is to be counted as 0, because of final calculation?

In this order-form we have only line 1 and 2 programed to have drop-down box in line Naziv robe (Name of the product). If I putted same drop-down boxes in rest 8 columns, #N/A would be shown in all programed cells bellow, like in line 2...

Kind regards from Serbia

Hello,

I am constructing a make-shift database in excel but can't seem to find a way to get magic trick #213 to work.

What I am trying to do is create a dynamic filter via 16 criteria. I would like any results that match all criteria that I specify and then displays results in a separate table to the right.

Thank you so much for your help - I would attach my spreadsheet to this message if I knew how.

BR,

PR

http://www.youtube.com/watch?v=tqCEY5YMyqw

I am trying to duplicate the concept on my spreadsheet. It is conceptually the same, the formula is slightly different.

Mine is as follows:
{=IF(ROWS(A$5:A5)>$B$2,"",
INDEX(US!A$5:A$72,
SMALL(IF(US!$AU$5:$AU$72=Sheet1!$A$2,ROW(US!$AU$5:$AU$72)-ROW(US!$AU$5)+1),ROWS(A$5:A5))))}

Resulting in a #VALUE! error. Can anybody advise me what should the formula me?

Thank a million!
Long

Looking for a little help tweaking Excel Magic Trick 185. I've reached what is probably just a mental block.

I'm trying to adjust a spreadsheet for work using the processes described in Excel MagicTrick 185. (Great series, by the way...) The catch is that the source data table can not be modified under any circumstances (we've already asked).

The problem is different than EMT_185 in that there are 9 columns that can potentially define a match as opposed to just the one shown in EMT_185. There are two user options ('I-III' and 'A-C') which in combination establish the headings for the 9 possible conditions (I-A, II-A, III-A ...) .

(See the linked spreadsheet) I've been able to:
determine if a row in the source matches user provided conditions (col Y),
get the count of matched source rows (row 26),
determine the match instance (col AA), and
place the source data in the output table (col AC)

What I can't quite get my mind around is suppressing the blanks in the output (i.e.:getting match 1 on row 1, match 2 on row 2, etc.)

What am I missing? Please help. A spreadsheet showing what I have so far is located on google docs. Perhaps you could make a new EMT out of it!

BTW.. this used to be done via vba, however that is no longer an option (don't ask... It just isn't, and it's not a topic that is open to discussion -- we tried).

Hello,

With the help of YouTube videos I have been trying to compare 2 lists in order to extract records that are not exactly the same on both lists, ideally onto 3 new worksheets (NEW, GONE, AMENDED).

I have been trying to implement your lessons, those of ExcelisFun, with lookup, arrays and a variety of more complicated formulas, to no avail.
I actually wonder if the issue is the type of data (text, number) or the length of the compared lists.

The current lists we have will soon grow to

Here is the scenario:

* List #1 has current active members (FirstName, LastName, Company, email) - 4 alphanumeric columns & approx. 3800 rows

* List #2 (an Outlook export) is a complete & historical list of members, including previous members who HAVE LEFT -4 alphanumeric columns & approx 5000 rows

**PS this list will soon grow to near 20000 rows

OBJECTIVE:

1) identify NEW members (on #1 but not #2)

2) identify members who left (on #2 but not on #1)

3) identify members who may have changed company and/or email (#1 does not correspond to #2) AND highlight what that change is so that list #2 can be updated accordingly

4) CAUTION: some members may have same First & Last name but are in fact different individuals (ex: Veronique Boisvert). I am not sure how to tackle this one: she won't come out as an exact match, but she is not NEW or GONE either. I suppose however that since this is exceptional, we could "humanly" review these odd cases.....

Can anyone help me figure this out? I have probably spent close to 20 hours so far and got so close to the solution by using the info in the videos (and ExcelisFun videos 369, 528, 539, 540) but there always seems to be a glitch !!

Many thanks

In reference to:

Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula.

Can this be done for more than one Lookup Value? I have this table of information that I would like to keep adding data to over time. Then based on the date and the team or teams within that table I would like to retrieve the data. e.g. The Table

Date Age Gender Position Sport Athlete Test 1 Test 2 Test 3 Test 4 1/8/2009 18-24 Female C Team 1 Lilly Padd 8 35 2.5 22 1/8/2009 32-38 Male A Team 2 Shaun Peet 9 38 2.6 18 1/8/2009 25-31 Male A Team 1 Dave Woodhead 11 41 2.7 20 1/8/2009 25-31 Male A Team 3 Mike Metcalf 13 44 2.8 17 1/8/2009 49-55 Male B Team 1 Doug Newell 12 32 2.45 16 1/8/2009 25-31 Female C Team 2 Julie Drew 6 31 2.1 15 1/8/2009 18-24 Male B Team 3 Chad Avrit 12 30 2.56 12 1/8/2009 18-24 Male B Team 2 Ben Fetzer 18 29 2.71 14 1/8/2009 32-38 Female C Team 3 Beth Sige 7 28 2.8 10 1/14/2009 18-24 Female C Team 1 Lilly Padd 9 32 2.34 23 1/14/2009 32-38 Male A Team 2 Shaun Peet 10 37 2.63 17 1/14/2009 25-31 Male A Team 1 Dave Woodhead 12 44 2.79 22 1/14/2009 25-31 Male A Team 3 Mike Metcalf 13 47 2.85 21 1/14/2009 49-55 Male B Team 1 Doug Newell 13 35 2.56 19 1/14/2009 25-31 Female C Team 2 Julie Drew 7 32 2.19 19 1/14/2009 18-24 Male B Team 3 Chad Avrit 13 28 2.33 14 1/14/2009 18-24 Male B Team 2 Ben Fetzer 19 29 2.54 20 1/14/2009 32-38 Female C Team 3 Beth Sige 6 30 2.78 17

and the retrieval area

1/14/2009 Athlete Test 1 Test 2 Test 3 Test 4 Team 1 Lilly Padd 9 32 2.34 23 Team 2 Shaun Peet 10 37 2.63 17 Team 3 Dave Woodhead 12 44 2.79 22 9 Mike Metcalf 13 47 2.85 21 Doug Newell 13 35 2.56 19 Julie Drew 7 32 2.19 19 Chad Avrit 13 28 2.33 14 Ben Fetzer 19 29 2.54 20 Beth Sige 6 30 2.78 17

in the retrieval area cells have this array formula (changes based on location):

=IF(ROWS(B$34:B34)

Hey guys

A while back I found a link (I think through this forum) to a few macros that were magic tricks.

Does this ring any bells? I'm trying to find it again.

Any help would be much appreciated.