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

[anu_gv]

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 1	Header 2 (Parameters)	Header 3 (1M)	Header 4 (6M)	Header 5 (12M)	Header 6 (24M)
Patient 1	Wt	50	55	75	100
Patient 1	Length	90	95	110	140
Patient 1	Blood Test	+ve	+ve	-ve	-ve
Patient 2	Wt	40	50	75	90
Patient 2	Blood Test	+ve	+ve	-ve	-ve
Patient 3	Wt	50	60	70	95
Patient 3	Length	80	85	90	105
Patient 3	Cholesterol	10mg/dl	15mg/dl	20mg/dl	30mg/dl
Patient 3	Blood 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 1	Wt	50	55	75	100
	Length	90	95	110	140
	Blood Test	+ve	+ve	-ve	-ve
Patient 2	Wt	40	50	75	90
	Blood Test	+ve	+ve	-ve	-ve
Patient 3	Wt	50	60	70	95
	Length	80	85	90	105
	Cholesterol	10mg/dl	15mg/dl	20mg/dl	30mg/dl
	Blood Test	+ve	+ve	+ve	-ve

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

Patient 1	Patient 2	Patient 3
Wt	Wt	Wt
Length		Length
Blood Test	Blood Test	Blood Test
		Cholesterol

Thx
Anu

 

[anu_gv]

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.

 

[BobUmlas]

Hi Bob,
I am not clear on your suggestion. I don't have any unused column
Thanks.
Anu

Of course you have unused columns. Or are you using all 16,384 columns?

 

[DanteAmor]

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.

 

[anu_gv]

Of course you have unused columns. Or are you using all 16,384 columns?

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

Thx
Anu.

 

[BobUmlas]

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]

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]

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.

 

[anu_gv]

Book1
	C
21
Sheet1

 

[anu_gv]

Book1
	C
21
Sheet1

Hi Dante,
I have posted, I hope I did right by following instructions.
Thx
Anu.

 

[DanteAmor]

Something happened, the data is missing. You must select a range of cells from a data sample, click on xl2bb tool and paste here.