need to make a new sheet of triplicates

Hi all,

I am in need of making an excel sheet from 3 other separate worksheets into 1, merging in a way.

I have 3 separate worksheets that list account numbers in them, no duplicates in these separate sheets. I am trying to take these 3 separate sheets and only keep the account numbers that have appeared in all 3 sheets and make a new sheet displaying just the one account number. Is there a way to do this in excel? My account numbers are in the A column for all sheets.

Thanks.

Assuming the sheets are sheet1, sheet2, sheet3.

In sheet1 first spare column (I have assumed col-D): In D2 and copy down:

Formula:

Please Login or Register  to view this content.

Change the "10" in A$10 to as many rows as necessary

The TRUE values in column-D correspond to the account numbers that appear in all three sheets.

Hopefully this is what you are looking for.

So my sheet names are October, November, December and each one has a different set of rows (October-600, November-585, and December-392). My blank sheet for the "merged" data would be the 4th sheet.

I think in order to modify the formula it should be the following: A1 blank column in the 4th sheet, the formula should be,

=AND(COUNTIF(October!A$2:A$600, A2)>0, COUNTIF(November!A$2:A$585, A2)>0, COUNTIF(December!A$2:A$392, A2)>0)

When I entered it in, I get the False result. Perhaps could it be the formatting of the cells? I noticed the account column, A, is formatted as general, instead of number.

First - just to confirm - the formula needs to be entered into the October or November or December worksheet, not the blank "merge" sheet.

Secondly - yes - if some of the account numbers are "numbers" and some are "text" then the formula will fail.

Can you upload a subset of your data? - just enough rows to show the formula returning FALSE when it should return TRUE.

To attach a file, first make sure to remove any sensitive/proprietary data and then click on “GO ADVANCED” and then scroll down to “Manage Attachments” to open the upload window. Choose your file then click on “Upload”, scroll down then click on “Close this window”, then “Submit reply”.

Better idea:

Paste the following formula in cell A2 of your empty "merge" sheet and copy down:

Formula:

Please Login or Register  to view this content.

You will then get account numbers in the rows of column-A of the "merge" sheet where the account number appears in all three months and blanks otherwise. You could then copy / paste-values and sort to get a consecutive list of account numbers.

You will still have a problem if some of the account numbers are text and others are numbers.

The attached workbook shows my simple test.

Thanks for helping me with this. I have just included a couple of rows of numbers here, and the formula on the third sheet. If you notice on the first 3 sheets, the account number 9527 and 9538 appear on each of the 3 sheets. This is the information I need so that I can ultimately make a mail merge document later on.

OK, thanks. You put the formula on the December worksheet in cell C2 but you didn't copy the formula down to C7. If you do that you will see TRUE against accounts 9527 and 9538 which I think is your desired result.

I also added my formula from post #5 to the "Merge Sheet" to A2 and then copied it down beyond A7 - check that out also.

Hope we're getting closer!

I have attached your workbook with the above modifications.