Need to create single list based on data stored in different worksheets, no duplicates

[Feicstur]

I am using an Excel workbook to collect a series of data. Each worksheet has data specific to a single group. I want to create a single master list based on the data created on each sheet and remove all the duplicates. Automated is best since the end users I am making this document for may not be able to handle dynamically using the Advanced Filter option.

Example Sheet2 A2:A34, Sheet3A2:A34 needs to create a list on Sheet1

[stanleydgromjr]

Feicstur,

Welcome to the Excel Forum.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

[mosesthetank]

I'm a relative newb here but I thought that I'd try to pay it forward and give this one a shot.

Please Login or Register  to view this content.

[stanleydgromjr]

Feicstur,

Detach/open workbook GetUniques w2 w3 A2A34 w1 ScriptingDictionary - Feicstur - EF983632 - SDG15.xlsm and run the GetUniques macro.


If you want to use the macro on another workbook with the identical setup, but, with different data:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniques macro.

[Feicstur]

Works Great Stanley!

I am noticing that when I edit the lists that feed the master list I get blanks sometimes, I am also trying to have the output go into several columns (example: A2:A50, B2:B50, C2:C50) Any thoughts?

[stanleydgromjr]

Feicstur,

Thanks for the feedback.

You are very welcome. Glad I could help.

I am noticing that when I edit the lists that feed the master list I get blanks sometimes, I am also trying to have the output go into several columns (example: A2:A50, B2:B50, C2:C50) Any thoughts?

In order to continue I will have to see your actual raw data workbook, with the raw data in its worksheets
Sheet2 and Sheet3, and, the results in Sheet1 (manually formatted by you for the results you are looking for).


To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.


If you are not able to supply your workbook, per the above, then:

In the Quick Reply box, just put the word BUMP. Then, click on the Post Quick Reply button, and someone else will assist you.

[Feicstur]

So the main sheet1 is called global parameters, the sheet2 is Unit 1 Consoles. The output sheet is master tag messages. I currently attached the macro to a button on the master tag message sheet. I added some logic to it to clear the cells before building the list since it did not appear to handle changes to the original lists too well (mainly when I would delete stuff from the main lists stuff got left behind on the master tag sheet).

For the Unit 1 Console sheet I had 3 sections of data that I mapped to a column Z which I plan on hiding from the users. Seemed easier then trying to build the macro to pull directly from the 3 separate sections.

I would really like to have this whole thing automated as those sections change, but not a deal breaker. I envision that the Unit 1 console sheet may be copied depending on the size of the customer facility but not sure if I am ready to handle the variables necessary to have the macro grow with the sheet as people copy the sheets to accommodate more data.

Hope this wasn't too convoluted.

[stanleydgromjr]

Feicstur,

Thanks for the workbook.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Assign the new macro to your command button Update Messages List.

Then run the GetUniquesV2 macro, by clicking on the command button Update Messages List.

[Feicstur]

very nice! thanks!

Am I pressing my luck if I ask for output to 3 columns (A2:C50)?

[stanleydgromjr]

Feicstur,

Thanks for the feedback.

You are very welcome. Glad I could help.

Am I pressing my luck if I ask for output to 3 columns (A2:C50)?

Let me look at the workbook again - be back later.

[stanleydgromjr]

Feicstur,

Range A2:C50 will hold 147 items.

After the unique items are found, and, downloaded beginning in range A2, and, sorted, they could be moved to fit in the given range A2:C50.

1. What happens if the count goes above 147 items?

2. Can I have another workbook that will have more than 49 unique items?

[Feicstur]

1. I don't anticipate the count going above 147. If it does very rare occurrence. Historically these category tops out closer to 100. Made the 147 count arbitrarily bigger then I expect it to be needed.
2. See attached.

[kayegap]

workshop 1.xlsxhi help work out these sheets have done it but it's seems some thing is missing

[stanleydgromjr]

Feicstur,

Thanks for the new workbook.

I have updated/renamed both macros, and, assigned them to the two command buttons.

Detach workbook GetUniquesV3 SandboxRev1 w2 w3 A2A34 w1 ScriptingDictionary - Feicstur - EF983632 - SDG15.xlsm and run the GetUniquesV3 macro, and, or, the ClearCellV2 macro, by clicking on the two command buttons.


And, in both macros, the new code is using the actual worksheet names.

If you want to use the two macros in another workbook with the same worksheet names, you will have to assign the new macros to the two command buttons.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Before you use the macros with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

[stanleydgromjr]

kayegap,

Welcome to the Excel Forum.


Your data structure is completely different from Feicstur's data structure.


Please do not post your questions in threads started by others - - this is known as thread hijacking.

Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.

After you start a NEW thread, then send me a Private Message with a link to your NEW thread, and, I will have a look.