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
Header 1Header 2 (Parameters)Header 3 (1M)Header 4 (6M)Header 5 (12M)Header 6 (24M)
Patient 1Wt505575100
Length9095110140
Blood Test+ve+ve-ve-ve


Header 1Header 2 (Parameters)Header 3 (1M)Header 4 (6M)Header 5 (12M)Header 6 (24M)
Patient 2Wt40507590
Blood Test+ve+ve-ve-ve


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

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
varios 10jun2020.xlsm
ABCDEF
1Header 1Header 2 (Parameters)Header 3 (1M)Header 4 (6M)Header 5 (12M)Header 6 (24M)
2Patient 1Wt505575100
3Patient 1Length9095110140
4Patient 1Blood Test+ve+ve-ve-ve
5Patient 2Wt40507590
6Patient 2Blood Test+ve+ve-ve-ve
7Patient 3Wt50607095
8Patient 3Length808590105
9Patient 3Cholesterol10mg/dl15mg/dl20mg/dl30mg/dl
10Patient 3Blood 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

Board Regular
Joined
Sep 11, 2015
Messages
62
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Jun 22, 2020
Messages
14
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Dear Ms. Anu_gv.

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

PatientHeader 1TestHeader 2 (Parameters)Header 3 (1M)Header 4 (6M)Header 5 (12M)Header 6 (24M)
Patient 1Patient 1TestWt
50​
55​
75​
100​
Patient 1TestLength
90​
95​
110​
140​
Patient 1TestBlood Test+ve+ve-ve-ve
Patient 2Patient 2TestWt
40​
50​
75​
90​
Patient 2TestBlood Test+ve+ve-ve-ve
Patient 3Patient 3TestWt
50​
60​
70​
95​
Patient 3TestLength
80​
85​
90​
105​
Patient 3TestCholesterol10mg/dl15mg/dl20mg/dl30mg/dl
Patient 3TestBlood 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:
FormulaPatientHeader 1
=IF(C5=C4,"",C5)Patient 1Patient 1
=IF(C6=C5,"",C6)Patient 1
=IF(C7=C6,"",C7)Patient 1
=IF(C8=C7,"",C8)Patient 2Patient 2
=IF(C9=C8,"",C9)Patient 2
=IF(C10=C9,"",C10)Patient 3Patient 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

New Member
Joined
Jun 22, 2020
Messages
14
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Dear Anu_gv,

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

TestPatient 1Patient 2Patient 3
Test_1WtWtWt
Test_2LengthLength
Test_3Blood TestBlood TestBlood Test
Test_4Cholesterol

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

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,130,037
Messages
5,639,670
Members
417,104
Latest member
Nelsini

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