+ Reply to Thread
Results 1 to 25 of 25

how to add cell if my partial text is existing in raw

  1. #1
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    210

    how to add cell if my partial text is existing in raw

    Please go through the attachment for requirement and help me with the right formula.

    regards,
    vimala
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how to add cell if my partial text is existing in raw

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: how to add cell if my partial text is existing in raw

    first sum the values in the column, after that

    with HLookup

    d12 =HLookup($C13,$A$2:$C$6,5,0)
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: how to add cell if my partial text is existing in raw

    Or

    try

    D12=SUMPRODUCT(OFFSET($A$2,1,MATCH($C12,$B$2:$C$2,0),COUNTA($A$3:$A$22))) and drag down!!
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: how to add cell if my partial text is existing in raw

    Another way array entered in D12 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Dave

  6. #6
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: how to add cell if my partial text is existing in raw

    I just gave example as three items in row 1, 2, 3 i have total rows upto 2000 where these basic and arrears basic will appear in different rows. here in the formulas it is just adding based on the header.. but it should identify out of 2000 rows where this basic is existing and for the header 3100 it should add... hope this is clear...

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: how to add cell if my partial text is existing in raw

    It is Always good to add a example that match your real question.

    Please add a new file and also add the expected result in your file.

  8. #8
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: how to add cell if my partial text is existing in raw

    Please find the new attachment with details...
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: how to add cell if my partial text is existing in raw


  10. #10
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: how to add cell if my partial text is existing in raw

    You have taken the required data to different region and gave "Yes" which is the unique source for the formula where i cant take the data to different place and give manual information and the formula is not only for basic it needs to the other rows also like Car + Car allowance, Loan + Loan Arrears etc...

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: how to add cell if my partial text is existing in raw

    Then fill in that criteria in the column L.

    I would solve this with a pivot table, but for that i should restructure the data.

  12. #12
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: how to add cell if my partial text is existing in raw

    yaah.... it sounds good.. let me try and get my output..

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: how to add cell if my partial text is existing in raw

    If I have interpreted correctly another way.

    Formula in D18 filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 08-10-2016 at 02:02 PM.

  14. #14
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: how to add cell if my partial text is existing in raw

    I have changed the headers and tried with the provided formula but it is giving me result as 0. can you please help me on this.

    attaching the template for your reference
    Attached Files Attached Files

  15. #15
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to add cell if my partial text is existing in raw

    This formula entered in D18:

    =SUMIF($A$3:$A$14,"*"&$C18&"*",B$3:B$14)

    Copy across to E18 then down to D19:E19.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  16. #16
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: how to add cell if my partial text is existing in raw

    No i cant give the formula to the exact column where office 1 and office 2 existing cause i have two hundred columns in my raw data and its not easy to identify where this office 1 is existing. but the first portion of your formula for the rows is good cause it will find the c18 and total the cells where as column i cant give the formula manually and more over every month my data get changes for example this month office 1 is in b column next month it may go to the J column.

    hope i didnt confuse you.

  17. #17
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: how to add cell if my partial text is existing in raw

    i have gave the example template with some different data columns... please refer and help me out.
    Attached Files Attached Files

  18. #18
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to add cell if my partial text is existing in raw

    Try this version...

    =SUMIF($A$3:$A$14,"*"&$C18&"*",INDEX($B$3:$C$14,0,MATCH(D$17,$B$2:$C$2,0)))

  19. #19
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: how to add cell if my partial text is existing in raw

    I am getting the result as #NA

  20. #20
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: how to add cell if my partial text is existing in raw

    Try

    d18=SUMIFS(INDEX($B$3:$F$14,,MATCH($C18,$B$2:$F$2,0)),$A$3:$A$14,"*"&D$17&"*") and drag towards the cells

  21. #21
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: how to add cell if my partial text is existing in raw

    the formula is working fine in the same sheet but if i am having the raw data in another sheet it is not giving me the right output.

  22. #22
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: how to add cell if my partial text is existing in raw

    Please Login or Register  to view this content.
    Then you have to add the sheetreference in your formula.

  23. #23
    Registered User
    Join Date
    08-23-2016
    Location
    New York, USA
    MS-Off Ver
    MS Office 2010
    Posts
    2

    Re: how to add cell if my partial text is existing in raw

    Sorry if it's a wrong place to ask, I just see no reason to add new thread because I guess my excel homework question is really easy, but I can't cope with it. I've exported some data to Excel and there are too many empty rows mixed with data. Please tell me how can I delete all the empty rows. If it's possible.

  24. #24
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: how to add cell if my partial text is existing in raw

    @dieter

    2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages.

  25. #25
    Registered User
    Join Date
    08-23-2016
    Location
    New York, USA
    MS-Off Ver
    MS Office 2010
    Posts
    2

    Re: how to add cell if my partial text is existing in raw

    Sorry. I'm newbie. I've noticed this question was answered today and was hoping for some Excel guru it will take few seconds to answer.

+ 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. Replies: 2
    Last Post: 03-01-2016, 08:01 PM
  2. Retrieve partial text from another cell
    By lookingforhelp1 in forum Excel General
    Replies: 3
    Last Post: 10-19-2015, 11:59 AM
  3. [SOLVED] Remove partial text from cell
    By shawnoot in forum Excel General
    Replies: 2
    Last Post: 07-13-2014, 11:33 PM
  4. [SOLVED] Match partial text when partial text is not exact
    By NamiSama in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-21-2014, 07:18 PM
  5. [SOLVED] Vlookup partial text in cell
    By rupss01 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-10-2014, 12:11 AM
  6. Copying row based on partial cell match and paste into existing worksheets
    By colinh69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2011, 02:42 PM
  7. matching partial text within one cell to partial text within another
    By Solstice in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 03-03-2010, 09:13 PM

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