Content Comparison

This describes the cross-check process between the master lists and Sierra to ensure we have all orders documented.

Team File

The Team file will have 2 tabs pmaster and emaster. These 4 tabs pmaster, emaster, psierra and esierra  The ones that have master will have abbreviated data pulled from the master list for the cross check process. You will need to run 2 Lists from Sierra, one for print and one for electronic and you'll put that data in the corresponding sierra tabs.

First, you'll need to sort each tab the pmaster and emaster tabs in the Team File by the fund code and note the various fund codes. I'm using the Collections team list as an example.  

...

If you have any entries with multi as the code just leave those off and highlight them pink.

Running Lists

 You'll use the Create Lists function in Sierra to run your lists.

...

When the export window opens click on Apply Saved Export at the bottom and then click on the Export Name to sort and choose ACQ_byFund (see Image 6 below) and hit Image RemovedSelect.

Image Added

Note that the Field delimiter is a tilde (~), and then click on Browse to select a location and name for your export file (I usually match the list file) so ACQ_COLL_printRev in this case (Image 7).

Image Added

Then hit OK and once the file exports you can go to Excel and open the file from there make sure the data type is delimited (Image 8)

Image Added

And hit Next, then make sure you but the tilde (~) in the Other box under delimiters (Image 9)

Image Added

Then hit Finish.

Cleaning Sierra Data

Once the review files is open in Excel (ACQ_COLL_printRev) copy the entire sheet and paste it into the corresponding sierra tab. In this case the psierra tab.

Once you do that you should do the following data clean up steps to make the process easier:

1. Sort the entire sheet by ORD Type
   1. Select entire sheet
   2. Hit Data menu at top, then select Sort
   3. Click "My data has headers" at top right
   4. Use Sort by pull-down to select ORD TYPE and hit OK
2. Replace the Order Type codes with the real types
   
   1. Select the entire ORD Type column 
   2. Do a Find and Replace (Ctrl+F)
   3. Under Search choose By Columns
   4. Now work your way through the codes to replace them with the types using your cheat sheet (see Image 10) by filling in the code and replacement text and hitting Replace All
      Image Added
      
3. Replace the codes in the Status column with the Status text the same way.
4. Cut the entire Record # column and paste it in front of the Title column so the record numbers are in Column A (see Image 11)

Image Added

Comparing Master List to Sierra

Next you'll go to your master tab (pmaster) in this case and do a match to the corresponding sierra tab (psierra) by following these steps:

1. Insert a blank column in front of Column A (Order #) in the pmaster tab.
2. Do a VLOOKUP formula in Cell A2: =VLOOKUP(B2,psierra!$A$2:$A$140,1,FALSE)
   1. The red numbers in the formula above will have to be changed based on the number of rows in your psierra tab
3. Then copy that formula into each row with data in the pmaster tab (see Image 12)
   1. Image Added
4. Now sort that sheet (pmaster) by Column A using the Data > Sort functions that you used before

5. Scroll down to the bottom and you should see all the non-matched rows at the bottom (#N/A will be listed in Column A rather than an order number)

   1. Highlight those non-matched rows in yellow (see Image 13)
   2. Image Added
6. Delete Column A (matching column) 
7. Now repeat the process to compare the sierra tab to the master tab for each type.