Saturday, June 18, 2011

Excel Concatenate Date Cells With Text Cells

You must first convert the referenced date cells to text, otherwise you end up with an unexpected result.
D3=TEXT(C3,"m/d/yy")&" "&B3

Result in cell D3 will be: 6/14/11 165

Sunday, June 12, 2011

Alternating Sort Using Excel

Q: How to sort a list of names alternating gender in ascending height order?
  1. Assuming the following are in different columns: First Name, Last Name, Gender, Height in Inches.
  2. Four level sort: 1) Gender, 2) Height, 3) Last Name, 4) First Name
  3. Create a helper column and do a series fill for the male names and a series fill for the female names. Both starting with the same number.  Start number should be high enough so that list of names does not increase place value.  Example: 300 male names series fill starting at 101, 5000 male names series fill starting at 1001.
  4. Create a second helper column that concatenates the first helper column (the series fill column) with the gender column.  Result should look like this: 101F
  5. Sort by second helper column (concatenated cells).
  6. Results should be in alternating gender order sorted by height.