Search a worksheet, compare it with 2nd worksheet, copy resuls to 3rd 1

[DCSage]

I have 2 spreadsheets to compare. I need to perform a search on 1 column, 'Episodes Missing from Master' in the Spreadsheet1 to see if any of the numbers appear in the Spreadsheet2. Everything has to be copied to Spreadsheet3, with details on the episodes found or not found. Spreadsheet3 requires a new column that features the Episodes found or not found. However, there are a few problems:

1) The Spreadsheet1's 'Missing from Master' column is a comma delimited list.

2). If the column 'Missing from Master' cell reads, ‘NOT ON SHEET’, then I need to read from the column 'Episodes' to actually get the number to search on Spreadsheet1.

3) The 'Episodes' column in Spreadsheet1 also features comma delimited lists in many of the cells!

What is the smoothest way to search, compare from the comma delimited lists, and then write the contents Spreadsheet1 plus the results to Spreadsheet3?

1) Is it best to first clean up the first worksheet, by splitting the values of the Comma Delimited lists in the column 'missing from cue sheet' and the 'Episodes' column of the Outside Spreadsheet

2) Perform the search and comparisons in the worksheets?

3) Then Copy results to the final worksheet?

My VB is rusty, as I have been programming in C# for years. But I imagine that I need to do a split function for my first option? Any suggestions are appreciated.

 

[SkipVought]

Hi,

Yes, there are!

1)

1) The Spreadsheet1's 'Missing from Master' column is a comma delimited list.

What does this mean? How can you have a comma delimited list in a column? Makes not senst at all.

Please be clear, concise and complete.

Please post a sample of data copied from your sheet, including headings. We need to know the structure of your sheet and the other sheets as well.

Use Data > Text to columns --DELIMITED --COMMA to parse any comma delimited data. Clean up your sheet FIRST, so that you can use native Excel features, rather than VBA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DCSage]

Thank you SkipVought!

I meant that within the column for 'Episodes' most cells have comma delimited lists.

I will try to clean up the sheet first. I have included a sample of my spreadsheet which should shed light on my tasks. I have sample vb script, but it in no way works.

I tried to upload my file, but could not, but the data from Sheet1 looks like the following:

The columns are :
PROGRAM EPISODES EPISODES MISSING FROM MASTER

Sample Data:

PROGRAM
Midnight Sky

EPISODES
P51349,P51350,P51351,P51352,P51353,P51354,P51355,P51356,P51357,P61358,
P61454,P61455,P61456,P61457,P61458,P61459,P61460,P61461,P61462,P61463,
P61582, P61583,P61584,P61585,P61586,P61587, P61588,P61589,P61590,P61591,
P61599, P61600,P61601,P61602,P61603,P61604,P61605,P61606,P61607,P71608, 71706,71707,71708,71709,71710,71711,71712,71713,71714,71715,71716,
71602, 71603, 71833, 71836A, 71838A, B106-081851, B106-081849, B106-081854, B106-081856

EPISODES MISSING FROM MASTER

P51349, P61454-61463, P61582, P61583, P61599, P61600, P61601, 71710, 71712, 71715, 71706, 71716, 71603, 71833, B106-081851, B106-081849, B106-081854, B106-081856

 

[SkipVought]

Parse

Select > Copy > Edit Paste Special --TRANSPOSE

Transpose into a new column.

Seems to me that each of these 'columns' of data is unrelated -- ie the value in a row is not directly related to any other value in the same row. YES?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DCSage]

I would like for each episode from the EPISODES MISSING FROM MASTER column to be on a separate row. I will still need to search in the actual Episodes Column if the data in the 'Missing from Master' column reads 'NOT ON LIST'

So instead of:

Sample Data:

PROGRAM
Midnight Sky

EPISODES
P51349,P51350,P51351,P51352,P51353,P51354,P51355,P51356,P51357,P61358,
P61454,P61455,P61456,P61457,P61458,P61459,P61460,P61461,P61462,P61463,
P61582, P61583,P61584,P61585,P61586,P61587, P61588,P61589,P61590,P61591,
P61599, P61600,P61601,P61602,P61603,P61604,P61605,P61606,P61607,P71608, 71706,71707,71708,71709,71710,71711,71712,71713,71714,71715,71716,
71602, 71603, 71833, 71836A, 71838A, B106-081851, B106-081849, B106-081854, B106-081856

EPISODES MISSING FROM MASTER

P51349, P61454-61463, P61582, P61583, P61599, P61600, P61601, 71710, 71712, 71715, 71706, 71716, 71603, 71833, B106-081851, B106-081849, B106-081854, B106-081856

I would have:

PROGRAM
Midnight Sky

EPISODES
P51349,P51350,P51351,P51352,P51353,P51354,P51355,P51356,P51357,P61358,
P61454,P61455,P61456,P61457,P61458,P61459,P61460,P61461,P61462,P61463,
P61582, P61583,P61584,P61585,P61586,P61587, P61588,P61589,P61590,P61591,
P61599, P61600,P61601,P61602,P61603,P61604,P61605,P61606,P61607,P71608, 71706,71707,71708,71709,71710,71711,71712,71713,71714,71715,71716,
71602, 71603, 71833, 71836A, 71838A, B106-081851, B106-081849, B106-081854, B106-081856

EPISODES MISSING FROM MASTER
P51349

PROGRAM
Midnight Sky

EPISODES
P51349,P51350,P51351,P51352,P51353,P51354,P51355,P51356,P51357,P61358,
P61454,P61455,P61456,P61457,P61458,P61459,P61460,P61461,P61462,P61463,
P61582, P61583,P61584,P61585,P61586,P61587, P61588,P61589,P61590,P61591,
P61599, P61600,P61601,P61602,P61603,P61604,P61605,P61606,P61607,P71608, 71706,71707,71708,71709,71710,71711,71712,71713,71714,71715,71716,
71602, 71603, 71833, 71836A, 71838A, B106-081851, B106-081849, B106-081854, B106-081856

EPISODES MISSING FROM MASTER
P51349

PROGRAM
Midnight Sky

EPISODES
P51349,P51350,P51351,P51352,P51353,P51354,P51355,P51356,P51357,P61358,
P61454,P61455,P61456,P61457,P61458,P61459,P61460,P61461,P61462,P61463,
P61582, P61583,P61584,P61585,P61586,P61587, P61588,P61589,P61590,P61591,
P61599, P61600,P61601,P61602,P61603,P61604,P61605,P61606,P61607,P71608, 71706,71707,71708,71709,71710,71711,71712,71713,71714,71715,71716,
71602, 71603, 71833, 71836A, 71838A, B106-081851, B106-081849, B106-081854, B106-081856

EPISODES MISSING FROM MASTER
P51350

 

[SkipVought]

Please post what you want the result to be IN TABULAR FORMAT, like you would see displayed on the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DCSage]

Hello.

Thank you for your The format was changed slightly, so I have 2 columns, but over 20,000 records. The user included the episode within the program after the hyphen. I need to write the results to a new worksheet. How can I export the below data:

PROGRAM EPISODE
THE BEST SHORTS-JSFA0701B JSFA0701B
CLASSIC CONCERTS-JCCA0702 - JCCA0702
STANLEY JORDAN

So that it is only:

PROGRAM EPISODE
THE BEST SHORTS JSFA0701B
CLASSIC CONCERTS JCCA0702

And write to a new worksheet?

Thanks.

 

[SkipVought]

You ought to end up with is TWO tables: [Program Episodes] & [Program Missing Episodes]

Each will have a column for Program and Episode.

So now let's go back to the data that you receive. Please explain in detail how that data is structured on the sheet. For instance...
[tt]
Column A contains both PROGRAM, EPISODES & EPISODES MISSING FROM MASTER, as posted originally, these 3 data items listed multiple times for different Programs.

Nothing else is on this sheet
[/tt]
Is this the case of something else?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DCSage]

Sorry, the requirements changed a bit for the data provided.

First worksheet master contains the following:

Column A currently lists the Program followed by a hyphen and the episode. Column B in this last draft, features the episode only.

PROGRAM EPISODE
THE BEST SHORTS-JSFA0701B JSFA0701B
CLASSIC CONCERTS-JCCA0702 - JCCA0702
STANLEY JORDAN


I need to extract the program only from Column A and then use column B to compare with the episodes in the second worksheet.


PROGRAM EPISODE
THE BEST SHORTS JSFA0701B
CLASSIC CONCERTS JCCA0702


The second worksheet contains the data to compare (although it's incomplete) with the first worksheet. This worksheet was revised as well and now features the data in one cell for the program column. Sometimes, there are several lines in a cell. The Program cell features the Episode number that I should use for the search:

Episodes Missing from Cue sheet
1. THE DEAL BDEA-08001 11/10/2008
2. THE DEAL BDEA-08002 11/11/2008
3. THE DEAL BDEA-08004 11/13/2008
4. THE DEAL BDEA-08005 11/14/2008
5. THE DEAL BDEA-08006 11/17/2008
6. THE DEAL BDEA-08007 11/18/2008
7. THE DEAL BDEA-08009 11/20/2008
8. THE DEAL BDEA-08010 SAIGON 11/21/2008
9. THE DEAL BDEA-08011 11/24/2008
10. THE DEAL BDEA-08013 11/26/2008

Each cell for this column is different.


Please tell me if this isn't clear. If there is a way to upload (I only see the box that says to type in the URL), then i would be happy to post.

Please advise.

Thanks.

 

[SkipVought]

Use Data > Text to columns --DELIMITED (-) to parse the Program only -- make other columns in the dialog as do not import. This way you only have the data to the LEFT of the first DASH, and NOTHING ELSE in column A.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DCSage]

Thank You!!!! This worked.