Remove Duplicates from Column B keep other column cell values in same position

anu_gv

Board Regular
Joined
Sep 11, 2015
Messages
62
Hello All,
I am trying to find solution for my problem. Searched into this forum for Duplicates topic, no luck for my problem.
I have sheet contains the below info as Table 1 (this is just an example, I have 3000 rows and 15 columns in my sheet). I am looking for Outcome 1 and 2 as example below. Any help is really appreciated.
For outcome 1, I have tried removing the duplicates from column <Header 1> it removes everything and keep unique value as one set. I don't want that result i would like to keep the position in same place after removing the duplicates, i don't know how to do it.
For outcome 2, I have tried to looking to vlookup and hlookup option, it did not worked out.

Table 1:
Header 1Header 2 (Parameters)Header 3 (1M)Header 4 (6M)Header 5 (12M)Header 6 (24M)
Patient 1Wt505575100
Patient 1Length9095110140
Patient 1Blood Test+ve+ve-ve-ve
Patient 2Wt40507590
Patient 2Blood Test+ve+ve-ve-ve
Patient 3Wt50607095
Patient 3Length808590105
Patient 3Cholesterol10mg/dl15mg/dl20mg/dl30mg/dl
Patient 3Blood Test+ve+ve+ve-ve

[Outcome 1] in one sheet:
Remove the duplicates from <Header 1> Column and keep only one row.
Header 1
Header 2 (Parameters)Header 3 (1M)Header 4 (6M)Header 5 (12M)Header 6 (24M)
Patient 1Wt505575100
Length9095110140
Blood Test+ve+ve-ve-ve
Patient 2Wt40507590
Blood Test+ve+ve-ve-ve
Patient 3Wt50607095
Length808590105
Cholesterol10mg/dl15mg/dl20mg/dl30mg/dl
Blood Test+ve+ve+ve-ve

[Outcome 2] in 2nd sheet: No. of test completed for patients.

Patient 1Patient 2Patient 3
WtWtWt
LengthLength
Blood TestBlood TestBlood Test
Cholesterol

Thx
Anu
 

anu_gv

Board Regular
Joined
Sep 11, 2015
Messages
62
Hi Dante,
Please ignore my previous question. When i read your Macro, i was able to figure out that <Sheet2> is not present in my workbook.
Also, I understood your suggestion that not to remove the duplicates from Column <Header 1>.
Is it possible to have removing the duplicates and filtering from column <Header 1> and get right side all the rows from <Header 2> to <Header 6> Columns.
Let me know if it is doable.

Thanks
Anu.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,623
Office Version
  1. 2007
Platform
  1. Windows
Try the change I put in post # 10, with that your original requirement will work.
For your new requirement put the example of the result that you want. use XL2BB tool, look at my signature.
 

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181

ADVERTISEMENT

No I am not using all the column. I meant my main used columns do not have any empty cells.

Thx
Anu.
So reread my instructions. Say your last used "main" column is P. In column Q, put the numbers from 1 to whatever the bottom cell of your data is. Then sort by column B.Then run your macro, copmaring each cell in B with the next cell in B and clear it. Then Sort by column Q. Then clear column Q.
 

anu_gv

Board Regular
Joined
Sep 11, 2015
Messages
62
Try the change I put in post # 10, with that your original requirement will work.
For your new requirement put the example of the result that you want. use XL2BB tool, look at my signature.
Hi Dante,
Not sure about the difference after I add your suggestion from Post#10. I did not see the differences. May be I am not able to identify it. Your original suggestion works fine. Thank You.
For my new question, it is same as what I mentioned in the examples, only difference is after I remove the duplicates, If I select the cell value from Column A, i am not getting respective rows (Wt, Length & Blood Test) from Column <Header 2> to <Header 6>.
I hope this is clear, If not i can prepare separate sheet.

Thx & Best Regards
Anu.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,623
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Your original suggestion works fine. Thank You.
I'm glad to hear that it works for you. For the new request, put here an example of the result you want, use xl2bb tool.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,623
Office Version
  1. 2007
Platform
  1. Windows
Something happened, the data is missing. You must select a range of cells from a data sample, click on xl2bb tool and paste here.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,181
Messages
5,640,643
Members
417,159
Latest member
Mayozero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top