Calculating (4, Threads): 1%

[Deleted]

Hi Experts,

I have a Sheet1 which has 3 Columns (Column A with Product Type, Column B with Product ID, Column C with Product Name) and 1000 rows with data in it. I am using below formula to remove duplicate data and match with Product Name 'Grocery' and show this in Sheet 2.

=INDEX('Sheet1'!$A:$C,MATCH(0,COUNTIF('Sheet1'!$B$1:$B1000,'Sheet1'!$B:$B)+('Sheet1'!$C:$C<>'Sheet1'!$D$2),0),COLUMN(A1))

The formula is working fine in Sheet2 but i want to increase the no of rows to 1000 more and when i drag this formula down in excel sheet to increase and match with data in sheet1 which has increased to 2000 records it shows 'Calculating (4, Threads): 1%' and excel sheet hangs and automatically crash and close down.

P.S. - i have set my excel sheet to Manual Calculation but when i save this sheet after i update data in sheet2 by increasing the rows it hangs and close automatically.

Pls suggest if their is any alternative way to replace the above formula with some other formula and get same result and save excel sheet with big data.

[#2]

@Deleted 

Here is a small VBA solution.

If you have solved your problem with this solution, please mark it as the correct answer so that others can also get this information.
If this is not what you are looking for, please give us a quick feedback.

Nikolino
I know I don't know anything (Socrates)

[#3]

@NikolinoDE Thanks for your help and providing solution in the sheet.

i hv added your sheet attached with my actual data.

When i click on button i want to show output in separate tab as per product name with no duplicate records.

Can u pls modify your code and send me updated sheet again.

Thanks in advance.

[#4]

@Deleted 

Voici une solution avec la copie..

Si cela correspond à vos idées en tant que solution, veuillez la marquer comme la bonne réponse, afin que les autres informés puissent être informés ... mieux avec un like.:-)

Si vous n’avez pas aimé la solution proposée, veuillez donner un bref commentaire.


Nikolino
Je sais que je ne sais rien (Socrate)

[#5]

@Deleted 

As variant that could be like

with formula

=FILTER(A1:C2000,C1:C2000=$I$1)

[#6]

@SergeiBaklan thanks but its giving 400 error when i click on button

[#7]

@Deleted 

Not sure which exactly errors do you have, but perhaps your version of Excel doesn't support dynamic arrays. 

[#8]

@NikolinoDE thanks this is not giving result as expected.

Actually i want to show result in separate Product Name Sheet Tab created (Grociery, Veggie, Fruits).

When i click on button the code should read and analyse my original data and should filter only unique data excluding duplicate and copy the same in respective product sheet as per product name.

The original data should remain like that only and it should not get deleted as i will keep on adding data in the original sheet.

[#9]

.RemoveDuplicates

[#10]

@Deleted 

Last try ... hope to help you...see file 🙂

Nikolino
I know I don't know anything (Socrates)

[#11]

@NikolinoDE Nope.. this is not correct.

anyways thanks.. this is not expected result.

i want to filter Tabelle1 data and copy all the records without duplicate in separate tab sheet filter by Product Name (Groceries, Veggie, Fruits)

[#12]

Data /
no duplicates
now you can adjust whether it is placed in the same place or somewhere else;

[#13]

Data / Filters / Special Filters
no duplicates
now you can adjust whether it is placed in the same place or somewhere else;

[#14]

@NikolinoDE thanks but i couldn't find the sheet attached

[#15]

No problem, the main thing is that you were able to find a workable solution that you are satisfied with.
Always at your service

Nikolino
I know I don't know anything (Socrates)

[#16]

@NikolinoDE 

i have data below and want to show result like this. appreciate if u can u pls modify the code and send me updated sheet .

Data			Result
Name	Type		Name	Type
Apple	Fruit		Apple	Fruit
Apple	Fruit		Potato	Vegetable
Potato	Vegetable		Rice	Food Grain
Potato	Vegetable		Grapes	Fruit
Rice	Food Grain		Tamatos	Vegetable
Rice	Food Grain		Dal	Food Grain
Grapes	Fruit		Banana	Fruit
Tamatos	Vegetable
Dal	Food Grain
Banana	Fruit

[#17]

Without VBA
Copy the whole list to another sheet
Select the area (list) with the left mouse button
Go to
Data
filter
Extended
Special filter
No duplicates (click)
OK

And now you have no duplicates in your list.

Quick and easy ... if this is not desired, please give a short feedback.
Glad to help how I am helped.

Nikolino
I know I don't know anything (Socrates)

[#18]

@NikolinoDE  thanks but i want code to do this result. can u modify your code and send me updated sheet as er result.

[#19 · Accepted solution]

@Deleted 

Everything simplified here with VBA ... press the button and everything is copied over ... everything? ... almost, duplicate entries not :-).

If you liked my solution, please mark it as the correct answer ... if possible with a like so that everyone else can be informed. If you did not like my answer, please ignore it and of course I apologize that I took your time.

Wish you a nice day and for further questions about your topic, I am at your disposal.

Nikolino
I know I don't know anything (Socrates)

[#20]

@NikolinoDE Dude fantastic.. thanks this is what i need.. kudos to u.