Results 1 to 6 of 6

Thread: noob wanting help with formulas

  1. #1
    Neophyte Daddypop's Avatar
    Join Date
    Jan 2014
    Location
    South Wales, U.K
    Posts
    1
    Articles
    0

    noob wanting help with formulas



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

    Hello guys

    This has probably been asked already, but i'm new to this forum and new to formulas in excel

    Anyway here goes:

    I have a sheet with Raw Data in it, what I want to do using a formula, "not a macro", Is to copy data from the "Raw" data sheet in to another sheet called "cleansed" using a text reference in one of the columns within the "Raw" data

    So the text is in column "F" but i want to copy the whole data in the row and move it to the Cleansed Sheet.

    Am i making sence or talking total pants

    Hope i've not confused anyone

    Look forward to hearing from you

    Daddypop

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    So are you saying that if your raw data sheet has anything in Col F on a particular row then that row should referenced by a formula in the cleansed sheet ? Why cant you sort the Raw Data Sheet by Col F, and then copy all the rows with a value in F across to your cleansed sheet?

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    The kind of thing you are asking for would be much better accomplished with a macro.
    And I like Hercules1946 answer of sorting the sheet, you could also try selecting all of your data and filter it by a specific value in column F, then manually copy to your new tab.

    If you are set on a formula you could try something similar to this.

    Provided your data on rawdata sheet has headers, start on row2, also assume that row1 in cleansed sheet has headers to match rawdata sheet.
    cell f1 in the following on the cleansed sheet should contain your text reference to search.
    =IF(RawData!$f2=$f$1,RawData!A2,"")
    then copy that for all cells in the row that you have data for.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Simi View Post
    The kind of thing you are asking for would be much better accomplished with a macro.
    And I like Hercules1946 answer of sorting the sheet, you could also try selecting all of your data and filter it by a specific value in column F, then manually copy to your new tab.

    If you are set on a formula you could try something similar to this.

    Provided your data on rawdata sheet has headers, start on row2, also assume that row1 in cleansed sheet has headers to match rawdata sheet.
    cell f1 in the following on the cleansed sheet should contain your text reference to search.
    =IF(RawData!$f2=$f$1,RawData!A2,"")
    then copy that for all cells in the row that you have data for.
    Hi Simi
    Im probably missing something, but why are you testing the RawData Col F data values (i.e. F2, F3, F4 etc) = Row $F$1 Header in the cleansed sheet

  5. #5
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    I am doing that so he can change what to search for in the raw data table. It isn't a pretty solution, but it fits the not VBA criteria.
    I would not recommend doing this as it is very messy to look at, and could be accomplished very easy with VBA.

    I have attached a sample book showing what this looks like.
    Note that field F1 on the Cleansed tab can be changed depend on what you want to search for on the Raw Data column F.
    Also note I have no error checking for F1 it really should be a drop down populated from the results of the raw data column f, but there you go.
    Attached Files Attached Files

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Simi View Post
    I am doing that so he can change what to search for in the raw data table. It isn't a pretty solution, but it fits the not VBA criteria.
    I would not recommend doing this as it is very messy to look at, and could be accomplished very easy with VBA.

    I have attached a sample book showing what this looks like.
    Note that field F1 on the Cleansed tab can be changed depend on what you want to search for on the Raw Data column F.
    Also note I have no error checking for F1 it really should be a drop down populated from the results of the raw data column f, but there you go.
    Simi
    Thanks for the clarification. As you say its not a "bells and whistles" result, but bearing in mind the end user limitations, its an excellant solution.

Posting Permissions

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