If, return, unless

[Adamd325]

Hey, so i have an issue with some data.
I have two tabs in the same excel.
On TAB 2 in A column, i need it to find data in column I of TAB 1 but only IF a value appears in column F of TAB 1. I also need this to pull only 1 value per cell and not to pull duplicate values if possible.
Does anyone know how this would be done?
As i am trying to automate what is currently a manual task.

 

[plettieri]

Adamd325

You might try setting up a helper column and use the UNIQUE and FILTER functions to get unique values where Column F has data. I believe these functions are available on 365.

Hope this helps
plettieri

 

[RobP]

Hi,

you might need to share a bit more in terms of your data on the sheets (so we know what you're looking for), but maybe this can help : If F has an "x" in it, it pulls column I.

=FILTER(Sheet1!I2:I100,Sheet1!F2:F100="x","")

Sheet1

Book1
	D	E	F	G	H	I	J
1						Data
2			x			abc
3						def
4			x			ghi
5						jkl
6
Sheet1

Sheet2 Result :

Book1
	A	B
1	abc
2	ghi
3
Sheet2

Cell Formulas
Range	Formula
A1:A2	A1	=FILTER(Sheet1!I2:I100,Sheet1!F2:F100="x","")
Dynamic array formulas.

 

[Peter_SSs]

Is it this? (If not, some dummy sample data and the expected results with XL2BB* should help)

* If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.

Adamd325.xlsm
	F	G	H	I
1
2	x			Item 1
3				Item 2
4	g			Item 1
5				Item 2
6	23			Item 3
7	a			Item 4
8				Item 5
9	w			Item 3
10				Item 4
11				Item 5
12				Item 3
13
Sheet1

Adamd325.xlsm
	A
1
2	Item 1
3	Item 3
4	Item 4
5
Sheet2

Cell Formulas
Range	Formula
A2:A4	A2	=UNIQUE(FILTER(Sheet1!I2:I12,Sheet1!F2:F12<>"",""))
Dynamic array formulas.

 

[Adamd325]

Hey all, Rob’s answer worked here, aside from a couple of issues, firstly it has pulled 90% of the data correctly however there are a few entries that fit the criteria that are not pulling, any idea what could cause this?
Secondly, we have data in other fields that doesn’t move down with the formula, meaning the data on sheet 2 in columns B-F move out of alignment with column A when the data its pulling changes as Sheet 1 is dynamic.
Thank you again

 

[Adamd325]

Update - I have solved the first problem - the second one is the only issue now
I can’t seem to pin other columns to the dynamic cells so the values move up and down and delete together for whatever reason

 

[Peter_SSs]

Secondly, we have data in other fields that doesn’t move down with the formula, meaning the data on sheet 2 in columns B-F move out of alignment with column A

You might need to explain in more detail or give some examples since your original question just asked to pull values into column A of TAB 2 from column I of TAB 1.

On TAB 2 in A column, i need it to find data in column I of TAB 1 but only IF a value appears in column F of TAB 1

There is no mention of anything in other columns or wanting anything in other columns

 

[Adamd325]

hey, apologies, i have tried to upload images of my problem but i will try to explain. TAB 1, is a master tab with thousands of rows of data, column F has 5 options, I only need it to pull column I back to TAB 2 provided a specific value is shown in column F, lets say this value is X.
TAB 2 houses detailed data about the cases, so the case numbers that are received from column I in TAB 1 would have manual data entries next to each once the new cases are pulled, i need this manual data to move with the data in column A of TAB 2 as its related to it, and similarly it should disappear with it if something on TAB 1 changes and the value in F changes to make the case no longer relavant.
Basically it tracks how long each case has been on TAB 2 and the age of it versus TODAY, so we know when it reaches a certain age the data can be deleted from TAB 1 and subsequently it should auto delete from TAB 2. The formula above works perfectly, but only for column A, columns B-G move out of alignment when a new ‘x’ value is found on TAB 1 or an x value is removed from TAB 1.
Hopefully this makes sense, again sorry I can’t post an image here.

 

[Peter_SSs]

I'm afraid that I don't understand enough to be able to set up any data to test. Did you try XL2BB as suggested in post #4? If you do, please don't give us thousands of rows, just a small set of dummy data and the expected results and explain again in relation to that small sample data.

 

[RobP]

Hi Adam,

firstly, Pete's right - we are a little blind here in terms of your data.
But secondly, your original post gave us just "one line" of information to follow .. (which thankfully I managed to interpret more or less well and get you what you asked for).

Looking now at your "new requirements" we see they are very much different to what you wanted us to achieve for you .. so as a guide, try to post more info in your first post to allow the entire problem to be looked at. That will give you more chance of successful outcome from one of the kind knowledgeable peeps on the forum . It maybe that you need to describe your workflow process also, to allow people to consider different angles. .. all of which is entirely possible here - but some sample data is real key to go with that, even if as Pete says, is just 10 rows or so with all the columns.

If I may hazard an attempt to understand your new need, the columns of data you manually inserted on Sheet2 alongside the "ever changing" column of data pulled from Sheet1 will never be able to be linked by row in Sheet2. It is simply because the formula I gave is operating only on a specific column of Sheet1 information, and doesn't care about sheet2. The formula has no idea about what you are entering in other columns on the sheet.

Thinking aloud here, if you have a process where you are manually typing info into a cell, then maybe it could be better to manually type the data into Sheet1.
If you think about it, you can still have your columns of "X" (presumably this means you want to add some data somewhere in a column based on what you have told us so far), so just filter the entire columns on your "X" column to show only those in the dataset, and type away .. That way you keep your entire dataset in one piece ... ?

cheers

Rob