Any Excel Geeks Here?

[thecharmed01]

I'm fighting with a spreadsheet and can't make it behave itself, just wondering if anyone here is an Excel guru or frequent user...

Is it possible, to 'match' text data in spreadsheets?

Like, I have one sheet which is a list of 'disc tracks' and another which is a list of 'DLC tracks' and I want to compare them and highlight the matching fields.

I'm doing this as I bought LRB the other day and was trying to figure out whether some songs on it, I had paid to DL.....
I want to make sure if it did, that I don't make the same mistake again.

If anyone can help I would be most appreciative!

[DrifterUK]

I'm fighting with a spreadsheet and can't make it behave itself, just wondering if anyone here is an Excel guru or frequent user...

Is it possible, to 'match' text data in spreadsheets?

Like, I have one sheet which is a list of 'disc tracks' and another which is a list of 'DLC tracks' and I want to compare them and highlight the matching fields.

I'm doing this as I bought LRB the other day and was trying to figure out whether some songs on it, I had paid to DL.....
I want to make sure if it did, that I don't make the same mistake again.

If anyone can help I would be most appreciative!

http://www.ecademy.com/node.php?id=85845

That should help

I would explain it, by I dont have time, because I have to go to college. If you still need help when I get home, I can explain then :)

[thecharmed01]

Ok, I'm heading to bed, it's way too late for my tired brain to comprehend right now, but I shall have a go tomorrow.

I have used VLOOKUP in the past at work, but usually numerical data which was why I wasnt sure if it would work for what I am trying to do.
I'm picking I need to create an extra column for essentially a duplicate yes/no which I will use for the VLOOKUP?
Would that be the easiest way to go about it?

Hopefully if I can get the formula working, whenever I add new DLC/Disk content, it *should* all update automatically and match me up with what I do/don't have.

Thanks heaps for the link though, I'll keep you posted as to how I get on :P

[sillystou]

Chew Excel gum and encourage Team Canada! :p

Just kidding. I used to use Excel when I was in college, but that was ages ago.

Hope you got through your problem though! :)

[DrifterUK]

Ok, I'm heading to bed, it's way too late for my tired brain to comprehend right now, but I shall have a go tomorrow.

I have used VLOOKUP in the past at work, but usually numerical data which was why I wasnt sure if it would work for what I am trying to do.
I'm picking I need to create an extra column for essentially a duplicate yes/no which I will use for the VLOOKUP?
Would that be the easiest way to go about it?

Hopefully if I can get the formula working, whenever I add new DLC/Disk content, it *should* all update automatically and match me up with what I do/don't have.

Thanks heaps for the link though, I'll keep you posted as to how I get on :P

Ive just done a quick mock up in Excel, using 2 columns on each sheet

On sheet 1, I had Song A in cell A1
On sheet 2, I had the same

In cell B1 on Sheet 1, I used this coding;
=VLOOKUP(A1,Sheet2!A1:A1,1,TRUE)

The A1 contains the data to be searched for, the Sheet2!A1:A1 is where it searches, the 1 is the column is the column that contains the result you need to be returned and the TRUE makes it an approximate match

So basically, if you set up the formula to read the name of the first song in your 'disc tracks' sheet, and the 'sheet!X:X' reference to search the column containing DLC track names, it will return any identical song names

I think that should do what you need to it to

[hawkofva]

Just for the record, all of the songs in Lego are exclusive. You can't download them elsewhere, so you don't need to worry about having already bought some of them.

[thecharmed01]

Ive just done a quick mock up in Excel, using 2 columns on each sheet

On sheet 1, I had Song A in cell A1
On sheet 2, I had the same

In cell B1 on Sheet 1, I used this coding;
=VLOOKUP(A1,Sheet2!A1:A1,1,TRUE)

The A1 contains the data to be searched for, the Sheet2!A1:A1 is where it searches, the 1 is the column is the column that contains the result you need to be returned and the TRUE makes it an approximate match

So basically, if you set up the formula to read the name of the first song in your 'disc tracks' sheet, and the 'sheet!X:X' reference to search the column containing DLC track names, it will return any identical song names

I think that should do what you need to it to

LOL You guys are funny!!! I'm cracking up here!

Drifter, you are awesome, thank you for the formula, I've just got up so am going to give it a go now mate!
Just a question though, if I seperated band name and track title, can I do a lookup formula looking for matches in both columns? Or am I restricted to one or the other.

I'm asking, as I believe there are multiple matching artist names - as there are often multiple songs by an artist, but there are also multiple tracks (by different artists) with the same track name which will give duplicates where they aren't if you get my drift?

I am contemplating whether to merge the cells and give one clear title to search on as atm I have;

Col 1........................Col 2
Artist........................Track Title

I'm thinking this may only work satisfactorily if I change it to;

Col 1
Artist - Track title

[DrifterUK]

LOL You guys are funny!!! I'm cracking up here!

Drifter, you are awesome, thank you for the formula, I've just got up so am going to give it a go now mate!
Just a question though, if I seperated band name and track title, can I do a lookup formula looking for matches in both columns? Or am I restricted to one or the other.

I'm asking, as I believe there are multiple matching artist names - as there are often multiple songs by an artist, but there are also multiple tracks (by different artists) with the same track name which will give duplicates where they aren't if you get my drift?

I am contemplating whether to merge the cells and give one clear title to search on as atm I have;

Col 1........................Col 2
Artist........................Track Title

I'm thinking this may only work satisfactorily if I change it to;

Col 1
Artist - Track title

Yeah, I think doing that will only work if the artist names and the track names are in the same column

[thecharmed01]

Ok, well I think I'll do that, am just using
=CONCATENATE(A2," - ",B2) real fast to put them back together then I'll try the VLookup!

[DrifterUK]

Ok, well I think I'll do that, am just using
=CONCATENATE(A2," - ",B2) real fast to put them back together then I'll try the VLookup!

Cool
Hopefully that should work :)

[thecharmed01]

Well, it's worked in that in one sheet, I have now placed two columns, one with the DLC list, and one with the disc list.

However, the VLOOKUP is returning some odd things, so I think I need to figure this out..........

[thecharmed01]

Ok, VLOOKUP is definitely not playing the game, I changed the formula slightly,
=VLOOKUP(E3,Sheet10!$A2:$A1172,1,TRUE)

Did this because the column to check against should remain constant $A2:$A1172 and only the column to look FOR should change.

However I am getting very weird results.

When I do the first cell, it's N/A which is cool, tells me there are no duplicates.
When I do the second cell, its returning an "Artist - Title" completely foreign to the cell it should be searching for!!!
Ugh!

[DrifterUK]

Ok, VLOOKUP is definitely not playing the game, I changed the formula slightly,
=VLOOKUP(E3,Sheet10!$A2:$A1172,1,TRUE)

Did this because the column to check against should remain constant $A2:$A1172 and only the column to look FOR should change.

However I am getting very weird results.

When I do the first cell, it's N/A which is cool, tells me there are no duplicates.
When I do the second cell, its returning an "Artist - Title" completely foreign to the cell it should be searching for!!!
Ugh!

That is odd. Im not sure why it would do that

Without having access to the spreadsheet, Ive got no idea what could cause it