Inserting a value from column based on another value

[bigalpha]

Two sheets in my workbook.

How do I insert a value from sheet 2 into a field on sheet 1, based on a value in sheet 1.

We input data on sheet 2; sheet 1 looks at that and returns YES or NO. I'd like to insert data associated with our inputted values from sheet 2 into a cell on sheet 1.

 

[NBVC]

Can you elaborate with an example or two? That explanation is a little confusing....

 

[bigalpha]

Sure. Sorry the example was confusing. I've attached the worksheet with some sample data.

Sheet 1 is the 'summary' page. It populates data from Sheet 2. It basically looks good and is a quick reference type thing.
Sheet 2 is where we put the numerical data.

In Sheet 1, when a sample result exceeds the regulatory limit, I'd like to populate it's Waste Code [column A] into a different cell [D54].

The problem I run into is when I have more than one sample that exceeds the regulatory limit; I'd like to get each Waste Code inserted into a text box, separated by commas.

 

[NBVC]

You will need to add a UDF (user defined function). Hit Alt+F11 to get to VBA editor... insert a new module (Insert|Module) and paste the following code in the editor pane...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then in the summary sheet, remove Merged Cells from D54, and unwrap text in the cell.... then enter formula:

=SUBSTITUTE(TRIM(aconcat(IF($J$4:$J$45="YES",$A$4:$A$45,"")," "))," ",", ")

Confirmed with CTRL+SHIFT+ENTER not just ENTER (it is an Array Formula)

 

[bigalpha]

You will need to add a UDF (user defined function). Hit Alt+F11 to get to VBA editor... insert a new module (Insert|Module) and paste the following code in the editor pane...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then in the summary sheet, remove Merged Cells from D54, and unwrap text in the cell.... then enter formula:

=SUBSTITUTE(TRIM(aconcat(IF($J$4:$J$45="YES",$A$4:$A$45,"")," "))," ",", ")

Confirmed with CTRL+SHIFT+ENTER not just ENTER (it is an Array Formula)

Thanks a lot for the help! I'm going to read through the Array Formula page and learn about them. I also think I understand how the code itself works, too.

In the code, why do you have it specified between a range and an array?

 

[Brianwarnock]

The code is designed to be flexible enough to handle being passed a range, an array or a single value.

Brian

 

[bigalpha]

The code is designed to be flexible enough to handle being passed a range, an array or a single value.

Brian

I don't understand the difference between an array and a range.

 

[NBVC]

Thanks for the assistance Brian,

BigAlpha, the actual code is not written by me... it is credited to Harlan Grove and was written in 2002... to be as Brian stated, flexible enough to work with ranges or arrays...

I, and many others, use this code frequently for this kind of problem. The basic code is designed to just do the concatenating and adding separator. I needed to add further functions to check for "YES" and remove FALSE results, etc...

 

[Brianwarnock]

In this instance an array is a particular data type

http://msdn.microsoft.com/en-us/library/kskex131(v=vs.80).aspx

Brian

Actually a google search using

array data type vba excel

Will return many hits which may be preferable to the one I posted which was the first in the list

 

[bigalpha]

Thanks for the assistance Brian,

BigAlpha, the actual code is not written by me... it is credited to Harlan Grove and was written in 2002... to be as Brian stated, flexible enough to work with ranges or arrays...

I, and many others, use this code frequently for this kind of problem. The basic code is designed to just do the concatenating and adding separator. I needed to add further functions to check for "YES" and remove FALSE results, etc...

Yeah, I saw the Trim and Substitute additions to the If formula and the last line of the code which I assume is designed for this spreadsheet.

 

[bigalpha]

In this instance an array is a particular data type

http://msdn.microsoft.com/en-us/library/kskex131(v=vs.80).aspx

Brian

Actually a google search using

array data type vba excel

Will return many hits which may be preferable to the one I posted which was the first in the list

Yeah, I did a google search but it didn't clear much up for me. Where I'm confused is that it seems a range and array are essentially the same things - a list of data that is more than 1 cell.

 

[Brianwarnock]

Just because a range of cells is an array, as in VLOOKUP doesn't mean that all arrays are a range of cells. The split function is used to create an array in VBA and is used in Access VBA , the addressing of an element in an array data type is by an index eg myarray(I) normally 0 would return the first element.

So yes your statement is both correct but incomplete, the function is allowing for both types of array, the cell range and the datatype.

Brian

 

[bigalpha]

I see. I believe that mostly makes sense in my brain. Thanks for your assistance!