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 <style><!--table {mso-displayed-decimal-separator:"\,"; mso-displayed-thousand-separator:"\.";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {color:#222222; font-size:13.0pt; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0;}--></style> 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 <!--StartFragment--> <colgroup><col width="87" style="width:65pt"> </colgroup><tbody> <!--EndFragment--></tbody> <colgroup><col></colgroup><tbody> </tbody> 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 . <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[sandy666]

I removed duplicates from tables and got this

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

your expected result contain duplicates , see subject e

 

[Melaccio]

Hi sandy666, thank you for your answer. However it's not this what I am looking for.
I expect that the formula automatically removes, in the column A of the sheet output, the blank cells and the duplicates. Just to have order results.
Do you have any idea how to attach file in this post, just to give you an example of what I am looking for?

 

[sandy666]

use OneDrive, GoogleDrive or any similar to share excel file and post link to this file here

btw. I will give you PowerQuery (Get&Transform) solution not a formula. Let me know if you are interested in...

 

[Melaccio]

use OneDrive, GoogleDrive or any similar to share excel file and post link to this file here

btw. I will give you PowerQuery (Get&Transform) solution not a formula. Let me know if you are interested in...

Hi sandy666, below the link:

https://drive.google.com/file/d/1afRZ7oq0lrHApnsdcX65cwaLdu19Wden/view?usp=sharing

However I need mostly a formula to solve this issue. If you have any tips how to integrate the options, delete duplicates and white cells, into the formula listed in the output sheet, it will be better.

 

[sandy666]

for formula solution you need to wait for someone else

have a nice day

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...excel-formula-combine-in-different-sheet.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Melaccio]

Cross posted https://www.excelforum.com/excel-fo...excel-formula-combine-in-different-sheet.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Thank you Fluff for the tip, I will do the same for the next posts.

 

[Marcelo Branco]

Hello Mellacio,

I'll try to help you. I suggest you create a helper column in the input sheet, something like this:

input

	A​	B​	C​
1​	Input​		Helper​
2​	subject n​		x​
3​	subject 5​		x​
4​	subject w​
5​	subject 1​		x​
6​	subject 2​		x​
7​	subject e​		x​
8​	subject e​
9​	subject 6​		x​
10​	subject 4​		x​
11​	subject s​
12​	subject w​
13​

<tbody>
</tbody>

Formula in C2 copied down
=IF(COUNTIF(A$2:A2,A2)=1,IF(OR(ISNUMBER(MATCH(A2,'table array 1'!A:A,0)),ISNUMBER(MATCH(A2,'table array 2'!A:A,0))),"x",""),"")

output

	A​
1​	Results​
2​	subject n​
3​	subject 5​
4​	subject 1​
5​	subject 2​
6​	subject e​
7​	subject 6​
8​	subject 4​
9​

<tbody>
</tbody>

Formula in A2 copied down
=IFERROR(INDEX(input!A$2:A$12,AGGREGATE(15,6,(ROW(input!$A$2:$A$12)-ROW(input!A$2)+1)/(input!$C$2:$C$12="x"),ROWS(A$2:A2))),"")

Hope this helps

M.