Results 1 to 15 of 15
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Looking Up Column Headings (2002)

    Seems like a simple problem, but my lookup skills are not up to solving it:

    My worksheet has 500 rows and 26 columns. Column A is a list of people's names. The headings of Columns B-Z are the names of membership lists on which the person appears. The names are arbitrary, but they are time sequenced from left to right. (The earliest list is in Column B, next one is in C, etc.)

    When a person's name appears on a list, his rank on that list is entered in the cell where his name (row) and the name of the list (column) intersect. (If you go down a column, you encounter an entry for each of the people who appear on that list. You will encounter them alphabetically, and each entry will show the person's rank on the list.)

    A person's name can appear on one list or several, but the group will always be contiguous from left to right, and there are never gaps. (Once the person leaves the lists, they never return.)

    I want to insert two columns to the left of column A: one that shows the name of the first (leftmost) list on which the person's name appears, and one that shows the name of the last (rightmost) list on which the person's name appears. His rank on any of the lists is immaterial, though that is what happens to be entered in each cell that applies to him.

    To do this manually, one would start with the person's name and read from left to right until one finds an entry in a cell. Then one would look at the top of the column for the name of the list. Then one would insert the name of the list in the first of the two new columns.

    The second step would be to do the same to find the last (rightmost) list in which the person appears. One could work from left to right, taking note of the last column in which a number appears. One could also work from right to left, taking note of the first column (from the right) in which a number appears.

    Now -- how do I get Excel to do it?

    I CAN rename the column headings in some alphabetical order fashion, but I would like to avoid that if I can.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking Up Column Headings (2002)

    Lou
    ..Tricky
    Try the attachment - although I'm sure a few array gurus can do better <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    (For the formulae I used - column D needed to be empty)

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Looking Up Column Headings (2002)

    In addition to Andrew's way, here is a direct way (no need to have Col D Blank). They are Arrays (confirm with ctrl-shift-enter)
    In A2 use:
    <pre>=INDEX($1:$1,MIN(IF(NOT(ISBLANK(D2:I2)),COLUM N(D2:I2))))</pre>


    In B2:
    <pre>=INDEX($1:$1,MAX(IF(NOT(ISBLANK(D2:I2)),COLUM N(D2:I2))))</pre>


    Copy them down the rows.

    The will pick the first nonblank cell in the range for Col A, Col B will have the last non-blank cell in the range. There may be blanks within this range and the results will still get the leftmost and rightmost "filled one"

    Steve

  4. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Looking Up Column Headings (2002)

    Thanks, guys. I'll give both these methods a try. At first glance I don't see how they work, but as I use them, I'm sure that that understanding will come.

    I REALLY appreciate your help here -- I was NOT looking forward to reinventing this particular wheel.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Looking Up Column Headings (2002)

    Problem:

    I got Andrew's method to work fine (after finagling the column numbers to match my actual worksheet), but I can't make yours return anything but the column heading of the column that the formula is in. Recognizing that operator error is the most common cause of things not working, I carefully checked everything against your original post, and I think I have copied it correctly, allowing for the differences in column numbers.

    Any suggestions? (Or maybe a working example like Andrew's?)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Looking Up Column Headings (2002)

    Did you confirm the entry with ctrl-shift-enter?

    To check this: Does the formula have "squiggley brackets" around the formula:
    {=index(....)}

    These are added by excel when you enter an array formula.

    If not, press edit(<F2>) then use ctrl-shift-enter to confirm (not just <enter>)

    Steve

  7. #7
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Looking Up Column Headings (2002)

    Aha! I did it, and it seems to work now.

    In the manner of operator-error-makers everywhere, I had previously done a ctrl-shift-enter a time or two, but I didn't clearly understand your instructions about it, and it didn't seem to do anything, so I undid it and didn't try it again.

    Thanks once more.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #8
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Looking Up Column Headings (2002)

    Your method now works fine in my original worksheet. Thanks again for the method and the clarification. Some observations:

    1) The curly brackets seem to disappear if I try to edit the formula in a cell. I guess I'll have to remember to ctrl-shift-enter.

    2) Andrew's method has an unexpected side effect: if there is an inadvertent gap in the sequence of entries from left to right, his method returns zeroes in the min and max columns. This allowed me to find some typos.

    3) Your method is clean, but requires(?) the user to pay attention to ctrl-shift-enter if anything is edited.

    4) Andrew's method quirkily requires a blank column before the "real" column range, and two blank columns after it, but it doesn't have the ctrl-shift-enter business.

    5) I'm assuming that I could replace the column ranges with range names, in both methods. That's my next experiment.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Looking Up Column Headings (2002)

    I can see making a range name for the header row, but why make a named range for every row in the spreadsheet that has data?

    It uses memory and it makes the formulas not "copyable" from row to row.

    Steve

  10. #10
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Looking Up Column Headings (2002)

    Good question. Also, it doesn't seem to work.

    (I like to use names in confusing formulas, since it often helps in understanding them later.)

    I've got a wheel that I didn't have to reinvent. I think I'm going to use it "as-is."
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Looking Up Column Headings (2002)

    You could create your own function (add this to a module)

    <pre>Function NonBlank(rLookup As Range, rValues As Range, bFirst As Boolean)
    Dim iCells As Integer
    Dim x As Integer
    Dim iFirst As Integer
    Dim iLast As Integer
    Dim iStep As Integer
    iCells = rValues.Count
    If bFirst Then
    iFirst = 1
    iLast = iCells
    iStep = 1
    Else
    iFirst = iCells
    iLast = 1
    iStep = -1
    End If
    For x = iFirst To iLast Step iStep
    If Not IsEmpty(rValues(x)) Then
    NonBlank = rLookup(x)
    Exit Function
    End If
    Next
    NonBlank = CVErr(xlErrNum)
    End Function</pre>


    In A2 enter:
    <pre>=NonBlank($E$1:$J$1,E2:J2,TRUE)</pre>

    to get the leftmost

    and in B2 enter:
    <pre>=NonBlank($E$1:$J$1,E2:J2,FALSE)</pre>

    to get the rightmost.

    No ctrl-shift-enter, No issues with gaps, no extra blank columns

    Will prompt upon opening about macros however, though you could add it into your personal.xls. If so then you would use:
    <pre>=PERSONAL.XLS!NonBlank($E$1:$J$1,E2:J2,TRUE )</pre>

    and
    <pre>=PERSONAL.XLS!NonBlank($E$1:$J$1,E2:J2,FALSE) </pre>


    Hope this helps,
    Steve

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Looking Up Column Headings (2002)

    Range names should work. Note, in both are formulas we use the entire row1 so if you name it it must include col A no matter what the range you choose to lookup in. Both of us based it on the actual column number not the column "index" within the range.

    Steve

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Looking Up Column Headings (2002)

    <P ID="edit" class=small>(Edited by sdckapr on 13-Apr-04 11:49. corrected typo)</P>I will explain mine and leave Andrew's to him (though they are "similar")
    <pre>=INDEX($1:$1,MIN(IF(NOT(ISBLANK(D2:I2)),COLUM N(D2:I2))))</pre>


    can be broken into parts:
    NOT(ISBLANK(D2:I2))

    is an array of 6 values checking to see if a cell isblank or not. If a cell is "not blank" it will be true, if blank, false. This array will be 6 values that are either TRUE (cell is not blank) or FALSE (cell is blank)


    IF(NOT(ISBLANK(D2:I2)),COLUMN(D2:I2))
    This IF is also an array of 6 values, if the value of the NOT(ISBLANK(D2:I2)) is TRUE it will give the column number for that column, otherwise (since no "false part" is given), it will give FALSE. This will give a column number (if cell is not blank) or FALSE (cell is blank)

    MIN(IF(NOT(ISBLANK(D2:I2)),COLUMN(D2:I2)))
    This will look at the array of 6 values and get the minimum of the values (the leftmost column with a value)

    =INDEX($1:$1,MIN(IF(NOT(ISBLANK(D2:I2)),COLUMN(D2: I2))))
    The final aspect is the INDEX function which looks in row 1 and gets the value from the column that is left most.

    Similarly
    <pre>=INDEX($1:$1,Max(IF(NOT(ISBLANK(D2:I2)),COLUM N(D2:I2))))</pre>


    Gets the Max column with a value (right most column)

    Steve

  14. #14
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Looking Up Column Headings (2002)

    I'm going with the plain wheels, even though the function would be cool.

    I've put a comment in the top row of each formula column, inviting attention to the need for ctrl-shift-enter. If and when I revisit this worksheet, I'll (hopefully) see the comment and remember all this stuff.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  15. #15
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking Up Column Headings (2002)

    Aren't time zones wonderful - a whole conversation while I was asleep <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
    For the record Lou

    My method used a SUMPRODUCT which is a 'form' of Array function (no curly brackets) but which is generically more limited than full array formulae.
    For each 'element' of the range it had three parts multiplied together
    1 the column number
    2 A truth test to check that the cell had content (has a value 0 or 1)
    3 A truth test to check that the cell 1 to the left (or right) didn't have content (has a value 0 or 1)

    In your description there was only one column that could satisfy conditions 2 & 3 - so only one returned a non-zero result. SUMPRODUCT then added up all results to return the only one that was a number - the target column. Index simply then looked it up on ROW 1. (The unintended side-effect was to find where your data didn't match your description <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.)

    As an aside, when dealing with nested functions, I find it a useful learning to 'explore' the innermost functions on there own (separate cell somewhere with a leading "=") to see what they do.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •