How to combine two arrays into one, format and then write to a range?

[Snowflakes2021]

Hi, I have two arrays (two customer ID lists), which have empty ("") values and duplicate values. How do I combine the two arrays, remove the empty values and duplicate values into a new array and then write the values to a range? Thanks.

[Marc L]

Hi,

an easy Excel beginner way if each list has a single column :
• merge the lists to a single column
• to copy the value to a range use an advanced filter on the merged column with the unique option to True …

[a2b1c1d1e1f1]

I have two arrays

Please attach some small sample data (xls file).

[Snowflakes2021]

Thanks for the response. But I have to use VBA to do that... as it needs to be performed without manual operations.

[Marc L]

As post #2 can be done under VBA so you can start operating manually with the Macro Recorder activated in order to get your own code …

[mohadin]

Hi
ID list 1 in sheet1
ID list 2 in sheet2
result in sheet3

Please Login or Register  to view this content.

[Snowflakes2021]

I need to combine the cust ID in column A and D starting in row 5 into one list, for example, column A has cust ID 1,2,3,4,5,6; column D has 3,5,8,10

Then remove the empty cell values and duplicates and then write the values into the next tab (e.g., column B starting in cell B6). I need to use VBA to avoid manual operations.

[jindon]

Try

Please Login or Register  to view this content.

[Snowflakes2021]

I got syntax errors for the rows with "Transpose".

[jindon]

How many rows do you have?
If below doesn't work, I need to see your workbook.

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

[Snowflakes2021]

It works, can you please add code to sort the values before write them into column B? I forgot to include this in my previous post. Thanks much!

[Snowflakes2021]

Try

Please Login or Register  to view this content.

It works! I got syntax errors for the "Transpose" from another post, not from this one. Can you please add codes to sort the values before write them into column B? Thanks much.

[jindon]

Change to

Please Login or Register  to view this content.

[Snowflakes2021]

Change to

Please Login or Register  to view this content.

I got error 13: type mismatch...for With [b6].Resize(UBound(e) + 1)

[jindon]

1)
Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

2) I really need to see your workbook since the code was tested and working here before I post.

[Snowflakes2021]

1) learning to post and will use best practice...(new user)
2) how to attach workbook? (again very new user)

[jindon]

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Snowflakes2021]

Please see attached file. The "before" tab has two columns of data to be combined, duplicates and "" removed, sorted and then write to the "after" tab. The data in there is what's desired after the scripts are run. Thanks!

[jindon]

So, different sheet.
Alter sheet names to suite.

Please Login or Register  to view this content.

[Snowflakes2021]

awesome, this works! thanks much!

[a2b1c1d1e1f1]

Another method:

Please Login or Register  to view this content.

[Snowflakes2021]

Thank you for your response!

[Snowflakes2021]

hi jindon, the macro (posted by you at 10:36 am 03-25) works perfectly for Form control (click button), but not working for activeX control, is there a solution? thanks!

[jindon]

See attached.

[Snowflakes2021]

thanks again!

[mohadin]

Well
Same but...