Hi there

I have a specific problem.

I have two worksheets, each with vertically arranged data in three columns. For example sheet 1 = A – b – 5 / B – b – 3 / C – a – 6.5 and sheet 2 = D – a – 4 / E – b – 7 / F – f – 8. Into the third worksheet I wish to extract data from columns A and B of sheets 1 and 2 but only those that are accompanied by a value equal to or above 6.


In other words, if there was such a function I would see in columns A and B in the third worksheet C – a / E – b and F – f.
Can I do this in Excel?

Thanks for your help

.
Here is one method :

In Sheet 3, A1, paste formula : =IF(Sheet1!A2>=6,"a","")
In Sheet 3, B1, paste formula : =IF(Sheet1!B2>=6,"b","")
In Sheet 3, C1, paste formula : =IF(Sheet1!C2>=6,"c","")
In Sheet 3, D1, paste formula : =IF(Sheet2!D2>=6,"d","")
In Sheet 3, E1, paste formula : =IF(Sheet2!E2>=6,"a","")
In Sheet 3, F1, paste formula : =IF(Sheet2!F2>=6,"a","")

Thanks for that.



I tried it but it doesn't quite do what I need. Ideally in the third sheet the formula / function would automatically place vertically the applicable values / data from sheet 1 and sheet 2 without every cell in sheet 3 only corresponding with an exact cell in one of the other two. It would be handy if I could put the retrieved values into alphabetical order as well.

If you want to collect data from multiple sheets into one sheet in the same workbook, you can apply the Consolidate function in Excel.

1. In a new sheet of the workbook which you want to collect data from sheets, click Data > Consolidate.
doc collect sheets into one 1

2. In the Consolidate dialog, do as these:

(1 Select one operation you want to do after combine the data in Function drop down list;

(2 Click doc select button to select the range of each sheet you want to collect;

(3 Click Add button to add the data range into the All references list box;

(4 Check the labels you use after combine data, and check Create links to source data option if you want to link the data in combine sheet with source data.
doc collect sheets into one 2

3. Click OK. Now the data have been collect and sum in one sheet.
doc collect sheets into one 3

It would be best if you attach a sample file of your data, sheets 1 and 2 and what you want the formula you are asking for in this forum to return in sheet 3.