LinkBack URL
About LinkBacks
I have data in multiple worksheets in specific columns.I want all of them to be in a two columns with no duplicates.Sample data and expected results are shown in Worksheet'Expected Result' in column A and B.
I have data in multiple worksheets in specific columns.I want all of them to be in a two columns with no duplicates.Sample data and expected results are shown in Worksheet'Expected Result' in column A and B.
How about (Run "jec"):
Please Login or Register to view this content.
Please Login or Register to view this content.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi. My ver
Please Login or Register to view this content.
An alternative to VBA is Power Query
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").Please Login or Register to view this content.
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Post# 3 & 4 doesn't seems to work as per expected results in Post#1.
Last edited by paradise2sr; 08-15-2022 at 11:53 PM.
Kindly add sheet name in code (As I will be combining specific sheet name and not all) with sorted alphabetically.Originally Posted by JEC.
Please Login or Register to view this content.
Thanx @JEC & @jindongot the results as expected. Post #1 remaining part adding specific sheet name & sorting alphabetically was accomplished perfectly by Jindon.
Please explain for #3 where you get the differance with the expected results.Post# 3 & 4 doesn't seems to work as per expected results in Post#1
Kindly enclosed the workbook with your code.My Last version of office is 2021.
Here my solution, including the sorting and predefined sheetnames.
Please Login or Register to view this content.
Thanx JEC
You're welcome!
And one with power query
Please Login or Register to view this content.
@paradise2sr
Kindly enclosed the workbook with your code.My Last version of office is 2021.
If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @Oeldere).
"Post# 3 & 4 doesn't seems to work as per expected results in Post#1
Please explain for #3 where you get the differance with the expected results."
Since you test the code, you can also give the reason what the differance is with the expected result.
I expect no differance, so I ask you to show it.
Pls find enclosed in attachment.There is #Value Error in Column B in Consolidated Sheet on running code as per your Post #3.This was the main reason,why it did not worked in my version of excel.
Value error.png
See my red text in the code.
"'it adds an @ before the formula and that result in "value"
'I remove the @ manualy from the formula and copied the date down."
See the attached file.
Oh,Now I got it.Thanx for the clearing the confusion
@paradise2sr
Thanks for the reply.
Hi@Jindon,
Refer to your Post #8 code,I found some data not mentioned above in Post #1 (enclosed in attachment) instead of extracting number is extracting whole text.
I have highlighted the with Red color.
Hence,I hope you can revised your code accordingly. Others things are fine.
Please Login or Register to view this content.
Worked well but one of my data earlier was resulted in correct way but now resulted wrong using this code.Kindly see what is missing.
Is this the last one?
Don't ask one by one.
Yes,it's last one.I have tested in all my private file with large data.
Add one line in bold
Please Login or Register to view this content.
Perfectly worked with no issue.Thanx
Please Login or Register to view this content.
Last edited by paradise2sr; 08-21-2022 at 09:27 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks