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]

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

Header 1	Header 2 (Parameters)	Header 3 (1M)	Header 4 (6M)	Header 5 (12M)	Header 6 (24M)
Patient 2	Wt	40	50	75	90
	Blood Test	+ve	+ve	-ve	-ve

Header 1	Header 2 (Parameters)	Header 3 (1M)	Header 4 (6M)	Header 5 (12M)	Header 6 (24M)
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
	Length	90	95	110	140

 

[DanteAmor]

varios 10jun2020.xlsm
	A	B	C	D	E	F
1	Header 1	Header 2 (Parameters)	Header 3 (1M)	Header 4 (6M)	Header 5 (12M)	Header 6 (24M)
2	Patient 1	Wt	50	55	75	100
3	Patient 1	Length	90	95	110	140
4	Patient 1	Blood Test	+ve	+ve	-ve	-ve
5	Patient 2	Wt	40	50	75	90
6	Patient 2	Blood Test	+ve	+ve	-ve	-ve
7	Patient 3	Wt	50	60	70	95
8	Patient 3	Length	80	85	90	105
9	Patient 3	Cholesterol	10mg/dl	15mg/dl	20mg/dl	30mg/dl
10	Patient 3	Blood Test	+ve	+ve	+ve	-ve
Sheet1

I did not understand.
The above is your data source?
Do you want an exit like what you put in post # 21?
Do you want 3 sections on the same sheet?
Each section on a different sheet?
Are you going to remove duplicates even with warnings?

 

[anu_gv]

Hi Dante,
After we remove the duplicates, if I apply filter on 2nd Row (Patient 1), I should get 4 rows. Now I am getting only 2nd row, the 3rd and 4th row for Patient 1 doesn't appear.
Likewise, for Patient 2 - row 5&6, Patient 3 - row 7-10.
I hope this is clear

Thx
Anu.

 

[DanteAmor]

if I apply filter on 2nd Row (Patient 1), I should get 4 rows. Now I am getting only 2nd row, the 3rd and 4th row for Patient 1 doesn't appear.
Likewise, for Patient 2 - row 5&6, Patient 3 - row 7-10.

I exposed it in post #6:
I recommend that you do not delete the duplicates, later you may have problems, to order or filter data, and others if you use other macros, which should consider those blanks.

 

[CA Vijay Gupta]

Dear Ms. Anu_gv.

For your Outcome 1, you can simple use if formula and outcome will be as below:

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

Solution:
# you only need a insert a new column (left or right side Header 1)
# Put an IF Formula like below:

Formula	Patient	Header 1
=IF(C5=C4,"",C5)	Patient 1	Patient 1
=IF(C6=C5,"",C6)		Patient 1
=IF(C7=C6,"",C7)		Patient 1
=IF(C8=C7,"",C8)	Patient 2	Patient 2
=IF(C9=C8,"",C9)		Patient 2
=IF(C10=C9,"",C10)	Patient 3	Patient 3
=IF(C11=C10,"",C11)		Patient 3
=IF(C12=C11,"",C12)		Patient 3
=IF(C13=C12,"",C13)		Patient 3

In the above Formula, my data Header is in Row no. 4 and Column A is Formula...


Points considered:
# Original Header 1 should be kept for future purposes


Just let me know if this solution has any unanswered query

 

[CA Vijay Gupta]

Dear Anu_gv,

For your Outcome 2, Just Check my Result using the same Date:

Test	Patient 1	Patient 2	Patient 3
Test_1	Wt	Wt	Wt
Test_2	Length		Length
Test_3	Blood Test	Blood Test	Blood Test
Test_4			Cholesterol

Just Check If fulfill your requirement, if yes, Let me explain all details to you..

Regards