noob wanting help with formulas

[Daddypop]

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

[Hercules1946]

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?

[Simi]

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.

[Hercules1946]

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

[Simi]

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.

[Hercules1946]

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.