+ Reply to Thread
Results 1 to 9 of 9

Filling blank cells in 2 cols based on specific text in adjacent column

  1. #1
    Forum Contributor
    Join Date
    04-25-2011
    Location
    Donegal, Ireland
    MS-Off Ver
    Excel 2013
    Posts
    133

    Filling blank cells in 2 cols based on specific text in adjacent column

    Hi guys,

    In 'Sheet 1' of the sample workbook are a list of products i receive on a daily basis.

    Cols D & E are partially filled and i have to manually fill the blanks.

    Up to now I've been doing this by using the Data Filter on Col C and using the 'if text contains' and if cells in Col D are blank, filter.

    So if the text in Col C contains *book* then Col D is 'Computers' and Col E is 'Notebooks
    So if the text in Col C contains *desk* then Col D is 'Computers' and Col E is 'Desktops'
    So if the text in Col C contains *MFP* then Col D is 'Printing' and Col E is 'Printers - Multifunction'
    So if the text in Col C contains *mono* then Col D is 'Printing' and Col E is 'Printers - Laser (Mono)'
    So if the text in Col C contains *monitor* then Col D is 'Monitors' and Col E is 'Monitors'

    Any remaining blank cells are filled with Col D 'Misc' and Col E 'Others'

    Sheet 2 shows the completed file

    Any assistance in getting this sorted would be very much appreciated and a real time saver

    Regards
    John
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    Earth
    MS-Off Ver
    2019
    Posts
    721

    Re: Filling blank cells in 2 cols based on specific text in adjacent column

    Formula for col D:
    Please Login or Register  to view this content.
    And for col E:
    Please Login or Register  to view this content.
    MatrixMan.
    --------------------------------------
    If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
    If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,720

    Re: Filling blank cells in 2 cols based on specific text in adjacent column

    Or with a macro
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    04-25-2011
    Location
    Donegal, Ireland
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Filling blank cells in 2 cols based on specific text in adjacent column

    Thx guys, the macro version works best for me but many thanks for your reply MatrixMan.

    Fluff13, the macro is perfect for my use, what a time saver. Can i ask though, if i want to add more search terms can it be on on separate lines or does it have to be on the same line and is it possible to add 2 search terms within the query i.e. 'laser' and 'colour'

    Regards
    John

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,720

    Re: Filling blank cells in 2 cols based on specific text in adjacent column

    For any new search terms you would need to add them to the formula and not sure what you mean by 2 search terms.

  6. #6
    Forum Contributor
    Join Date
    04-25-2011
    Location
    Donegal, Ireland
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Filling blank cells in 2 cols based on specific text in adjacent column

    If Col C contains the 2 words 'Laser' and 'Colour' anywhere in the phrase, then Column D would be 'Printing' and Col E would be 'Printers - Laser Colour'

    It's not that important and it works great the way it is.
    Last edited by JohnnyBoyxxx; 05-07-2019 at 09:40 AM.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,720

    Re: Filling blank cells in 2 cols based on specific text in adjacent column

    How about
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    04-25-2011
    Location
    Donegal, Ireland
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Filling blank cells in 2 cols based on specific text in adjacent column

    Brilliant, perfect, can't believe i've been doing this manually for so long.

    Many many thanks

    Cheers

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,720

    Re: Filling blank cells in 2 cols based on specific text in adjacent column

    You're welcome & thanks for the feedback

+ 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. Filling in Blank Cells Based on Reference of Other Cells
    By TeamOSupremeO in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-31-2018, 11:52 AM
  2. [SOLVED] Filling in blank cells in column
    By adamjohnson182 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-22-2014, 06:12 PM
  3. Replies: 2
    Last Post: 04-17-2014, 03:30 PM
  4. Combine text in multiple cells if adjacent column is blank
    By problematic in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-10-2012, 01:52 PM
  5. The macro for filling the text in blank column
    By ronlau123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-12-2011, 05:13 PM
  6. Replies: 4
    Last Post: 05-12-2011, 04:37 PM
  7. Filling blank cells based on neighbouring column
    By ppataki in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-02-2009, 06:28 AM

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