Results 1 to 3 of 3

Thread: Why is "Text">0 = TRUE?

  1. #1

    Why is "Text">0 = TRUE?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    My goal is to create tabular data from non-tabular sources. Each source table is 3 columns wide but # of rows varies, with header and record data mixed together, and the "Ref" record cells merge (example below). Seemed I could use a series of if statements (in what would be the final table) to identify which data existed on a particular row, then parse out duplicates in a pivot. It ran afoul because this formula, ="A1">0, evaluates to TRUE (top left in the example is A1). Why does text evaluate to >0? Is there a better way to accomplish my goal?

    How it imports from the source:
    Ref Report Reported by
    1.1 text goes here name goes here
    Priority Risk
    medium, for example low, for ex.
    response response text goes here (empty)
    Ref Report Reported by
    2.1 and on it goes...


    What I want it to look like:
    Ref Report Reported By Priority Risk Response
    1.1 text goes here name goes here medium, for example low, for ex. response text goes here


    thanks in advance for the help

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Assuming your source data is laid out consistently as per the 1.1. reference (i.e. that the text strings to pull are in the same relative position from the respective references), then it can be done with formulas.

    Assuming the table is in Sheet1, starting at A1, and your summary is in Sheet2, starting headers at A1...

    in A2 enter formula:

    =IFERROR(INDEX(Sheet1!$A$2:$A$20,SMALL(IF(ISNUMBER(FIND(".",Sheet1!$A$2:$A$20)),ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!$A$2)+1),ROWS($A$2:$A2))),"")

    adjust the range A2:A20 to suit your source range size, then confirm the formula with CTRL+SHIFT+ENTER not just ENTER, then copy down as far as necessary to capture all references.

    in B2 enter formula:

    =IF($A2="","",INDEX(Sheet1!B$2:B$20,MATCH($A2,Sheet1!$A$2:$A$20,0)))

    copied to C2, then down same distance as column A formula. (this is a regular entered formula).

    in D2 enter formula:

    =IF($A2="","",INDEX(Sheet1!B$2:B$20,MATCH($A2,Sheet1!$A$2:$A$20,0)+2))

    copied to E2 and down

    In F2 enter formula:

    =IF($A2="","",INDEX(Sheet1!B$2:B$20,MATCH($A2,Sheet1!$A$2:$A$20,0)+3))

    copied down.


  3. #3
    Thanks. The field layout is generally the same, though the number of records from one "Ref" to another varies and sometimes there will be a gap between one and the next. However, I see what you're doing with the function and the other equations so may be able to adapt them to work. May also try cell("type",) with AND conditions to prepare an intermediate table which can be filtered.

Posting Permissions

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