Topics

Sorting the data #poland


Ernest Fine <efine@...>
 

On reading the solution to this problem given by the Feldmans, I realized
that I had misread what was required. My "solution" would not work in this
instance. But it is an example of how to use an IF statement to select data
out of alternate columns.

Ernie Fine
Vienna, VA, USA

At 06:14 PM 9/2/99 -0400, Jim & Sara Feldman wrote:
Ada:

The answer I am about to give assumes that the records are
intermixed. That is, that M records are mixed with B and D records
and that there are separate columns for GROOM and BRIDE name. [snip]


David Sharfarz <dsharfarz@...>
 

Import the file into Microsoft Access, which is a database program with much
better sorting capabilities than Excel.

David Sharfarz

ada01@... wrote:

Dear JRIERS,

I am the Plockoordinator:-), and sent more than 14000 records during
the past year, while many more still to be posted in the future, mainly
from the Russian years of the Mormons microfilms >from the town of Plock.
I have a problem. I need your advise.
I have all the data sent to JRI database. It is in Excel. I want to sort
the data according the surnames and than the type and than the year.
I need to sort of differently when it is M - marriages.
When type is M, the sort key should be the surname of husband, but
the Surname of the bride should be under it.

How do I do that?

Thanks in advance for the tip, shalom,

Ada
-------------------------------------
E-mail: ada01@...
Date: 09/02/99
Time: 22:06:08
Web site:http://www.geocities.com/Paris/Rue/4017/
-------------------------------------

This list (jri-pl@...) is hosted by
JewishGen: The Home of Jewish Genealogy
Visit our home page at http://www.jewishgen.org
Visit JRI-PL at http://www.jewishgen.org/jri-pl/
---
You are currently subscribed to jri-pl as: dsharfarz@...
To unsubscribe send email to $subst('Email.Unsub')


MILTGOLD@...
 

In a message dated 9/2/99 3:33:23 PM, ada01@... wrote:

<<It is in Excel. I want to sort
the data according the surnames and than the type and than the year.
I need to sort of differently when it is M - marriages.
When type is M, the sort key should be the surname of husband, but
the Surname of the bride should be under it.
I would check an Excel manual, or perhaps the Microsoft.com site which will
direct you to technical support for Excel. But, >from my light Excel user
level--- isn't this just a matter of going to the toolbar, the sort function,
and setting the soft categories to be first one sort then the second sort
(Husband surname, and within that, Bride name)?

Milton R. Goldsamt
Research Statistician
miltgold@...


Ernest Fine <efine@...>
 

Ada, and others who are interested in solving this type of problem, the
easiest and quickest way to do this is to add another column to the
spreadsheet; this new column will contain either the surname of the
husband, or the surname of the bride. It then becomes one of the sort keys.

To create the contents of this column, an "IF" statement is used.

Let's say the spreadsheet looks something like:

column A = husband surname
column B = bride surname
column C = Type code
column D = new column for surname

In column D, in whatever row the data starts in, place the following
statement. For the sake of example, I am using row "5."

=IF(C5="M",A5,B5)

This will result in cell D5 being filled with the contents of cell A5 if
the type code is "M", and D5 being filled with the contents of cell B5 if
A5 is NOT "M." (If using a lower-case "m" - type that value instead of "M"
- it has to match exactly.)

Then, you will do a "fill down" on the formula, to place the formula in all
rows. To do this, click on the first formula cell (the one you just typed
the "IF" statement into, C5 in our example here), and drag, with the mouse,
down that one column so all rows are highlighted. Then perform the "fill"
operation - press the "Ctrl+D" keys.

(Alternatively, since this is a large spreadsheet, click on the first
formula cell, then scroll down to the last formula cell; hold down the
Shift key, and click that last cell. This will highlight all rows
inbetween. Then press the Ctrl+D keys for the fill operation.)

NOTE: the directions above are for Excel 97. Process is similar for other
spreadsheets but exact statements may vary.

Ernie Fine
Vienna, VA, USA

At 10:06 PM 9/2/99 PDT, you wrote:
Dear JRIERS,

I am the Plockoordinator:-), and sent more than 14000 records during
the past year, while many more still to be posted in the future, mainly
from the Russian years of the Mormons microfilms >from the town of Plock.
I have a problem. I need your advise.
I have all the data sent to JRI database. It is in Excel. I want to sort
the data according the surnames and than the type and than the year.
I need to sort of differently when it is M - marriages.
When type is M, the sort key should be the surname of husband, but
the Surname of the bride should be under it.

How do I do that?

Thanks in advance for the tip, shalom,

Ada
[snip]


Jim & Sara Feldman <feldmans@...>
 

Ada:

The answer I am about to give assumes that the records are
intermixed. That is, that M records are mixed with B and D records
and that there are separate columns for GROOM and BRIDE name. That
being the case, consider (and this is not the only solution):

First sort the list on the column containing M/B/D. Do it in
reverse order so that M comes out on top. (You could now simply copy
it to another spreadsheet to simplify your life but I am doing it one
sheet.) Now highlight the entire section containing M records and the
headings as well. Excel allows you to sort in order by three
different parameters (columns) so: Sort now FIRST on GROOM and SECOND
on BRIDE. You have what you asked for.

Jim



Dear JRIERS,

I am the Plockoordinator:-), and sent more than 14000 records during
the past year, while many more still to be posted in the future, mainly
from the Russian years of the Mormons microfilms >from the town of Plock.
I have a problem. I need your advise.
I have all the data sent to JRI database. It is in Excel. I want to sort
the data according the surnames and than the type and than the year.
I need to sort of differently when it is M - marriages.
When type is M, the sort key should be the surname of husband, but
the Surname of the bride should be under it.

How do I do that?

Thanks in advance for the tip, shalom,

Ada
-------------------------------------
E-mail: ada01@...
Date: 09/02/99
Time: 22:06:08
Web site:http://www.geocities.com/Paris/Rue/4017/
-------------------------------------



This list (jri-pl@...) is hosted by
JewishGen: The Home of Jewish Genealogy
Visit our home page at http://www.jewishgen.org
Visit JRI-PL at http://www.jewishgen.org/jri-pl/
---
You are currently subscribed to jri-pl as: feldmans@...
To unsubscribe send email to $subst('Email.Unsub')
Jim Feldman
14 Linda Lane
Newton, MA 02461

617-527-0509


ada01@...
 

Dear JRIERS,

I am the Plockoordinator:-), and sent more than 14000 records during
the past year, while many more still to be posted in the future, mainly
from the Russian years of the Mormons microfilms >from the town of Plock.
I have a problem. I need your advise.
I have all the data sent to JRI database. It is in Excel. I want to sort
the data according the surnames and than the type and than the year.
I need to sort of differently when it is M - marriages.
When type is M, the sort key should be the surname of husband, but
the Surname of the bride should be under it.

How do I do that?

Thanks in advance for the tip, shalom,

Ada
-------------------------------------
E-mail: ada01@...
Date: 09/02/99
Time: 22:06:08
Web site:http://www.geocities.com/Paris/Rue/4017/
-------------------------------------