Remove duplicate inside the cells

[shukla.ankur281190]

Hi Masters.

I am stuck with a problem. I have large data set and inside the cell there are too many duplicate values. I have solved this through formula but it take too much calculation time.

Suppose A1=11:21 11:21 11:21 11:29

Result B1=11:21 11:29

With helper column and formula it can solved but I suppose can it done with power query ?
Cheers
Ankur

[AliGW]

I think you have been around long enough to know that we need a sample workbook!!!

[shukla.ankur281190]

Ali, My friend asked this query me and i have gave his the solution through formula but he has very large data set, so formula solution take a long calculation time.

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H	I	J	K	L
1	Data	Result		Helper 1	Helper 2	Helper 3	Helper 4	Helper 5	Helper 6	Helper 7	Helper 8	Helper 9
2	11:21 11:21 11:21 11:29	11:21 11:29		11:21	11:29
3	15:56 15:56 23:57 23:54 23:37 23:37	15:56 23:57 23:54 23:37		15:56	23:57	23:54	23:37
4	15:56 15:55 23:57 23:57 23:37 23:37	15:56 15:55 23:57 23:37		15:56	15:55	23:57	23:37
5	15:56 15:56 23:57 23:57 23:37 23:35	15:56 23:57 23:37 23:35		15:56	23:57	23:37	23:35

Sheet: Sheet1

D2

Please Login or Register  to view this content.

B2

Please Login or Register  to view this content.

[AliGW]

That's fine. Please attach a sample workbook.

[JieJenn]

Maybe use VBA? Give this UDF a try.

Please Login or Register  to view this content.

[shukla.ankur281190]

Ok The sample is attached. I suppose that power query can done this query.

[shukla.ankur281190]

VBA is the last option for my friend, he said. He suppose to do it done with solution of other tool like power query ?

[samba_ravi]

B2

Please Login or Register  to view this content.

May be some one come with simple formula
The above will work up to 10 unique numbers

[shukla.ankur281190]

It is not working Siva

[sandy666]

sure, is that what you want?

for a quick.... without finesse

[shukla.ankur281190]

Sandy can you explain it step by step so that I can learn it, I saw your many solution through power query. What is the source to learn it ?

[samba_ravi]

It is not working Siva

See the attached file

[sandy666]

Wait a minute, I need coffee (sleepless night

[sandy666]

Ok,
How to load and split - you know

• because you've 4 rows so you need duplicate 1st table 4 times
• Change type will be added automatically, sometimes it is annoyed
• Transpose each Table to get rows to columns and now you've each source row transposed to column but each value is in separated row not together
• because you cannot remove duplicates from Table like this (it doesn't work correctly in this case, believe me ) you'll need single column for each table. Of course first column for first table, second column for second table etc...
• after that you can remove duplicates from these single columns in all tables

so far so good

• because you want result in rows so transpose each table again
• now Append Queries as new (will be clearly) all 4 tables (add from left to right window in order Table1, Table1 (2), etc...
• then you've 4 columns separated but you need all together so Merge columns (select all columns then merge) with space separator, chasnge name Merged to your own
• close&load as connection

• after that select cell where you want your result table will start
• right click on the Append table in Queries window
• select Load to..
• select Table
• select Existing worksheet (or new if you wish)
• ok

I hope I did not miss anything

[sandy666]

Sources:
https://support.office.com/en-us/art...9-c6d29cb8df6a

https://support.office.com/en-us/art...rs=en-US&ad=US

Google

Books /E-books

and of course tests, tests, tests.... and tests again

I would not be myself if I did not add

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.

[shukla.ankur281190]

Sandy If don't create duplicate and work on only single query, it is working good. I am surprised when I do add more data power query don't work? Can you please let me know hey it is happening ?

[shukla.ankur281190]

Have a look at this attached file.

I have created on one query, I does exactly what I was wishing but after loaded the query, when I do add more data and refresh the query It doesn't work ?

[sandy666]

• because you cannot remove duplicates from Table like this (it doesn't work correctly in this case, believe me ) you'll need single column for each table. Of course first column for first table, second column for second table etc...
• after that you can remove duplicates from these single columns in all tables

you didn't read my "description" post

you removed whole duplicated rows not duplicated values, ops wrong - you selected single column and remove dups, this what is duplicate in one column is not a duplicate in second and so on....

1 | 2 | 3
1 | 3 | 4
1 | 5 | 6

if you remove duplicates from first column you will stay with single row

btw. with new row of data you'll need add duplicate of first table and do the same what you did for the first four
like I said in post#10 : quick.... without finesse

[deciog]

shukla.ankur281190

For the formula of the colleague nflsales To work it is necessary to press CTRL + "SHIFT" + ENTER, it works perfectly

Decio

[sandy666]

@shukla.ankur281190

Ok, here is a simpler solution

Please Login or Register  to view this content.