Excel formula combine in different sheet

M

Melaccio

New Member
Joined
May 21, 2019
Messages
13
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="width: 115"]
<colgroup><col></colgroup><tbody>[TR]
[TD]table array 1 (sheet1)[/TD]
[/TR]
[TR]
[TD]subject 1[/TD]
[/TR]
[TR]
[TD]subject 2[/TD]
[/TR]
[TR]
[TD]subject 3[/TD]
[/TR]
[TR]
[TD]subject 4[/TD]
[/TR]
[TR]
[TD]subject 5[/TD]
[/TR]
[TR]
[TD]subject 6[/TD]
[/TR]
[TR]
[TD]subject 7[/TD]
[/TR]
[TR]
[TD]subject 8[/TD]
[/TR]
[TR]
[TD]subject 9[/TD]
[/TR]
[TR]
[TD]subject 10[/TD]
[/TR]
[TR]
[TD]subject 11[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 115"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Table array 2 (sheet2)[/TD]
[/TR]
[TR]
[TD]subject f[/TD]
[/TR]
[TR]
[TD]subject p[/TD]
[/TR]
[TR]
[TD]subject n[/TD]
[/TR]
[TR]
[TD]subject l[/TD]
[/TR]
[TR]
[TD]subject t[/TD]
[/TR]
[TR]
[TD]subject a[/TD]
[/TR]
[TR]
[TD]subject b[/TD]
[/TR]
[TR]
[TD]subject c[/TD]
[/TR]
[TR]
[TD]subject d[/TD]
[/TR]
[TR]
[TD]subject e[/TD]
[/TR]
[TR]
[TD]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-ignore:padding; 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>[TABLE="width: 87"]
<!--StartFragment--> <colgroup><col width="87" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 87"]lookup value (sheet-input)[/TD]
[/TR]
[TR]
[TD="class: xl63"]subject n[/TD]
[/TR]
[TR]
[TD="class: xl63"]subject 5[/TD]
[/TR]
[TR]
[TD="class: xl63"]subject w[/TD]
[/TR]
[TR]
[TD="class: xl63"]subject 1[/TD]
[/TR]
[TR]
[TD="class: xl63"]subject 2[/TD]
[/TR]
[TR]
[TD="class: xl63"]subject e[/TD]
[/TR]
[TR]
[TD="class: xl63"]subject e[/TD]
[/TR]
[TR]
[TD="class: xl63"]subject 6[/TD]
[/TR]
[TR]
[TD="class: xl63"]subject 4[/TD]
[/TR]
[TR]
[TD="class: xl63"]subject s[/TD]
[/TR]
[TR]
[TD="class: xl63"]subject w[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
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:

[TABLE="width: 92"]
<colgroup><col></colgroup><tbody>[TR]
[TD]subject n[/TD]
[/TR]
[TR]
[TD]subject 5[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]subject 1[/TD]
[/TR]
[TR]
[TD]subject 2[/TD]
[/TR]
[TR]
[TD]subject e[/TD]
[/TR]
[TR]
[TD]subject e[/TD]
[/TR]
[TR]
[TD]subject 6[/TD]
[/TR]
[TR]
[TD]subject 4[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]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 :confused:. [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
I removed duplicates from tables and got this

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]subject[/td][td][/td][td]expected?[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]subject 1[/td][td][/td][td]subject 1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]subject 2[/td][td][/td][td]subject 2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]subject 4[/td][td][/td][td]subject 4[/td][/tr]

[tr=bgcolor:#FFFFFF][td]subject 5[/td][td][/td][td]subject 5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]subject 6[/td][td][/td][td]subject 6[/td][/tr]

[tr=bgcolor:#FFFFFF][td]subject e[/td][td][/td][td]subject e[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]subject n[/td][td][/td][td=bgcolor:#FFFF00]subject e[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]subject n[/td][/tr]
[/table]

your expected result contain duplicates , see subject e
 
Upvote 0
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?
 
Upvote 0
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...
 
Last edited:
Upvote 0
sandy666 said:
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...
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
Fluff said:
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.
Click to expand...

Thank you Fluff for the tip, I will do the same for the next posts.
 
Upvote 0
Hello Mellacio,

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

input
[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Input​
[/TD]
[TD][/TD]
[TD]
Helper​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
subject n​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
subject 5​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
subject w​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
subject 1​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
subject 2​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
subject e​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
subject e​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
subject 6​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
subject 4​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
subject s​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
subject w​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Results​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
subject n​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
subject 5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
subject 1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
subject 2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
subject e​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
subject 6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
subject 4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
569
smozgur
smozgur
S
  • Question Question
Creating a dynamic Subject line from information in a particular Excel column
Replies
0
Views
662
StevieMP
S
S
  • Question Question
Macro to compare two sheets with identical column headers and first column, then showing all table differences in third sheet
Replies
1
Views
217
DanteAmor
DanteAmor
G
  • Question Question
Workbook formulas copied with Sheet references in new Workbook
Replies
0
Views
399
GK039
G
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
561
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top