+ Reply to Thread
Results 1 to 7 of 7

Finding the cell containing information

  1. #1
    Registered User
    Join Date
    04-03-2009
    Location
    Anchorage, Alaska, USA
    MS-Off Ver
    Office 365
    Posts
    46

    Finding the cell containing information

    Hi,

    Just like the Excel FIND feature (not the formula, the feature ie CTRL+F) - type in what you want, Excel finds it and highlights the cell.

    How do I do this in a formula?

    I keep getting results like how to use the FIND formula and how to select cells with formulas in them. Not what I want.

    I want :
    Pseudo-code : =FINDCELL("testword",0) (zero for case-insensitive, and since I'm making up a function here, why not).
    Result = C5, cause cell C5 contains "testword".

    Thank you.

    PS : The function of such a formula would be to "read" an imported document, find keywords, and then be able to extract relevant information from cells relative to that position.
    • Ex
      Import #1 "Pt Payments" is found at cell J5, so now I can use that knowing that column J, rows 6+ contain the payment data.
      Import #2 "Pt Payments" is found at J8 (cause something above it was taking up space), so now I can use that knowing that column J, rows 9+ contain payment data.
      Import #3.....found at L6 for whatever reason, ....

    Useful since imports from PDFs etc dont always space equally.
    Last edited by akedm; 02-11-2021 at 05:45 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    2016
    Posts
    549

    Re: Finding the cell containing information

    Maybe try conditional formatting if what you are looking for is in the cell values.
    pick a cell to type in your search word and use conditional formatting to higghlite the cell if the value matches your "search" cell's value.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: Finding the cell containing information

    Best thing is to post a SMALL sample excel file... that truly represents the unequal spacing from PDFs, and tell us what you want to happen to the data ...And provide a few manually-calculted results for checking.

    Guideline son posting files are in the yellow banner (top of page).
    Glenn



  4. #4
    Registered User
    Join Date
    04-03-2009
    Location
    Anchorage, Alaska, USA
    MS-Off Ver
    Office 365
    Posts
    46

    Re: Finding the cell containing information

    As requested here's files: A PDF of two statements (looks very nice when printed), and an Excel spreadsheet which is the result of Exporting the PDFs to Excel using Adobe Acrobat Pro DC (it looks very messed up).

    Thank you, Beamer. While conditional formatting will find cells, conditional formatting does not make the cell address available to use. What I need is to read the information and store it so I can use it, and to do that here I need cell addresses. In order for Excel to read the information Excel needs to know where to look, which is why I need a FIND formula that works like Excel's FIND feature, cause when a PDF is exported to a spreadsheet it looks very messed up.

    As not to confuse things, Excel has a FIND formula and a FIND feature - they are completely different. The FIND feature is activated by Ctrl-f and lets you find stuff in the worksheet, just like Word's FIND feature, and as far as I know there's no formula equivalent in Excel for that feature. On the other hand, Excel's FIND formula is activated by typing =find( into a cell and lets you find the word "dear" in the word "endearing".

    I need a formula to find the cell containing the information I want wherever it is in the spreadsheet, and grab information relative to that cells position. If the birth date is always in the cell below the words "Birth Date", then in a fixed-position system where G15 always contains "Birth Date" I know to always look in cell G16 for the birth date I need. My formula for grabbing that would be =G16.

    In a relative-position system where the birth date is always below the words "Birth Date", then I first need to find the cell address containing the words "Birth Date". How? Once I do that I can grab the information from relative row + 1 for the birth date I need.

    While the supplied PDFs look similarly formatted to our eye, here's what you'll find in the Excel file based on those PDFs :

    Sheet: Table 1 Sheet: Table 2
    DOB cell N6 cell K6
    Copay Visit Chg cell I18 cell H17
    Payment History:Date cell A22 cell A22
    Account Name cell K2 (contains Patient) cell I2 (does not contain Patient)

    How would I find the cell address of each of these phrases in Excel so I can use the relative-location searching to find the info I want to grab?

    Thank you.
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: Finding the cell containing information

    You can use an approach like this... However, it is not pretty...

    It was not obvious what field you want to return from Payment history date, so I didn't do anything there.

    =INDEX('Table 1'!$A$1:$Z$100,2+SUM(INDEX(('Table 1'!$A$1:$Z$100=D$1)*ROW('Table 1'!$A$1:$Z$100),)),6+SUM(INDEX(('Table 1'!$A$1:$Z$100=D$1)*COLUMN('Table 1'!$A$1:$Z$100),)))

    The numbers in red were determined individually for each parameter. They are (at least!!) standard for each term, based on a sample of n=2.
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: Finding the cell containing information

    Here's a more generic (draggable) version... and YES you will need to do a bit more with the dates as Excel has imported them with a 2080 DoB!!

    =INDEX(INDIRECT("'"&$A5&"'!A1:Z100"),B$1+SUM(INDEX((INDIRECT("'"&$A5&"'!A1:Z100")=B$3)*ROW($A$1:$Z$100),)),B$2+SUM(INDEX((INDIRECT("'"&$A5&"'!A1:Z100")=B$3)*COLUMN($A$1:$Z$100),)))
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-03-2009
    Location
    Anchorage, Alaska, USA
    MS-Off Ver
    Office 365
    Posts
    46

    Re: Finding the cell containing information

    INDEX...I need to get more familiar with that formula.
    Nice work! I'll need to look at it in a bit, working another problem right now.
    I'll keep you posted. Thank you so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Finding Incorrect Information
    By fa2020 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-05-2020, 10:35 AM
  2. Finding next row of information
    By toongal12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-02-2017, 02:52 PM
  3. Finding information within a t
    By SadKing in forum Excel General
    Replies: 1
    Last Post: 07-07-2016, 09:14 PM
  4. Replies: 1
    Last Post: 02-12-2015, 06:14 PM
  5. Excel 2007 : Finding Information on a Table
    By Mockngbird in forum Excel General
    Replies: 3
    Last Post: 10-10-2011, 10:51 AM
  6. Linking and finding information
    By Jarlhen in forum Excel General
    Replies: 11
    Last Post: 04-14-2009, 08:44 AM
  7. Finding information between two cells
    By DAVID1972 in forum Excel General
    Replies: 2
    Last Post: 07-11-2008, 08:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1