Excel formula combine in different sheet

[Melaccio]

Hello All,

I am trying to create an Output sheet that gives me the result from the Input sheet.
The input sheet match all the similar criteria with other two sheet (Table array 1 and Table array 2).
I tried to combine the formula in the Output sheet to use only one column, but to have order results (it means to have no duplicate criteria and no blank cells between the criteria cells) I need to use more then one column.

The file looks like this (but in this case is an example, since I need to use a formula that matches all the criteria of the column A of the sheet Table array 1, Table array 2 and Input)


table array 1 (sheet1)
subject 1
subject 2
subject 3
subject 4
subject 5
subject 6
subject 7
subject 8
subject 9
subject 10
subject 11


Table array 2 (sheet2)
subject f
subject p
subject n
subject l
subject t
subject a
subject b
subject c
subject d
subject e
subject e

lookup value (sheet-input)
subject n
subject 5
subject w
subject 1
subject 2
subject e
subject e
subject 6
subject 4
subject s
subject w

The formula that I am using in the column A of the sheet-Output, starting from the cell A2, is:

=IF(ISERROR(VLOOKUP(input!A2,'table array 1'!$A:$A,1,0)),IF(ISERROR(VLOOKUP(input!A2,'table array 2'!$A:$A,1,0)),"",input!A2),input!A2)

The result will look like:

subject n
subject 5
subject 1
subject 2
subject e
subject e
subject 6
subject 4



In this case, how can I combine this formula by removing duplicates and white cells in the column A of the sheet-Output?
Thank you for your time. I am trying to solve this issue from 1 month .

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jason.b75]

Assuming that the data in all of your sheets starts in row 2 (with headers in row 1), try this array formula in A2 of the Output sheet.

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

If you need the number of rows to vary as new data is added then I would suggest using dynamic named ranges for that purpose. Changing the ranges used to full columns is not a good idea, calculation times will be horrendously slow!

[Melaccio]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi Pepe, Thank you for your answer. I attach the file so it will be easy to understand what my idea is.

[Melaccio]

Assuming that the data in all of your sheets starts in row 2 (with headers in row 1), try this array formula in A2 of the Output sheet.

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

If you need the number of rows to vary as new data is added then I would suggest using dynamic named ranges for that purpose. Changing the ranges used to full columns is not a good idea, calculation times will be horrendously slow!

Hi Jason, Thank you for your answer. However the formula that you describe it's not working. I have attach a template of the file just to have an idea what I am planing to have.

[jason.b75]

The formula gives the exact results that you listed at the end of your first post. If it is not working for you then I would have to assume that you either did not enter it correctly or you are using an older version of excel than the one shown on your user profile.

Saying 'doesn't work' tells us nothing, what happens when you try to enter it? There is a significant difference between 'doesn't work' and 'doesn't work as expected'.
Can you enter the formula, or do you get a pop up message telling you that there is a problem with the formula?
Does the formula show an error (an incorrect result is not an error) in the cell? If so, which error? Errors would be in upper case and start with a # symbol.
Does the formula only work in the first row, but not when you fill down?
Are the results all blank?
Something else?

[Melaccio]

The formula gives the exact results that you listed at the end of your first post. If it is not working for you then I would have to assume that you either did not enter it correctly or you are using an older version of excel than the one shown on your user profile.

Saying 'doesn't work' tells us nothing, what happens when you try to enter it? There is a significant difference between 'doesn't work' and 'doesn't work as expected'.
Can you enter the formula, or do you get a pop up message telling you that there is a problem with the formula?
Does the formula show an error (an incorrect result is not an error) in the cell? If so, which error? Errors would be in upper case and start with a # symbol.
Does the formula only work in the first row, but not when you fill down?
Are the results all blank?
Something else?

The formula is working fine, what i would like to do is to integrate in the formula criteria that will make order. In this case deleting duplicates and white cells.

[Pepe Le Mokko]

Please don't quote entire posts unnecessarily. They are just clutter and make threads hard to read.
Use the "Quick reply" instead
Thanks

[dominicb]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)