Unique records

Solved/Closed

Kamal - Oct 28, 2011 at 01:17 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 12, 2013 at 10:29 AM

Hello,

I am very new to excel macros, however, I want to make a macro that will provide unique records from three different excel sheets.

I have employees listed in three different excel files. What I want is that when I copy three different excel sheet, the fourth sheet will provide me the unique records. I'm not sure if the employee IDs can also be recognized from sheets they belong to.

Please let me know if you have any questions. I would really appreciate if someone can help me out in making this macro.

Thanks & Regards
Kamal Hassan

Unique records
How to avoid duplicate records in sql select query - Guide
Medical records software free download - Download - Organisation and teamwork
Excel create unique id for each row - Excel Forum
Unique code to confirm your identity on facebook - Facebook Forum
How can I confirm my identity on Facebook from a lost phone number - Facebook Forum

29 responses

Answer 21 / 29

Kamal Hassan
Feb 14, 2012 at 10:05 AM

Hey Trowa

Thanks for the modified code, however, when I tired running the above code by pasting it in Module1, I'm getting a debug error and it's indicating ".LineStyle = xlContinuous" line in the code.

Can you please send me the spreadsheet you are using? Also, just take a look if somethng missed out in the code you provided above.

Thanks
Kamal Hassan

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 14, 2012 at 10:44 AM

Hi Kamal,

I don't get any errors when running the code.

Here is my workbook:
http://speedy.sh/JHrJa/Kamal-Unique-records.xls

Kind regards,
Trowa

Answer 22 / 29

Kamal Hasan
Feb 23, 2012 at 12:25 PM

Hi Trowa

Sorry for the delay in response.

I also worked on the spreadsheet you shared with me last week, its working fine for me too. Not sure why the code was not working for me previously.

Although, we are all set to move further, however, I still have some concerns with regard to the new code:

1. Please try running the code after removing the employees from NOACTION and INTERMITTEN_FMLA_RETURN_TO_WORK sheets OR MULT_RTW and INTERMITTEN_FMLA_RETURN_TO_WORK sheets OR NOACTION and MULT_RTW OR from all these three sheets.

2. Let the header be in all these three sheets while removing the employees. The header for NOACTION and MULT_RTW sheets will be:
TRANS_DATE TRANS_TIME UNIQ_ID SSN EE_ID FULL_NAME 6X RQST_LVE_BGDT LOA_RQST_LVE_BGDT 6X RQST_LVE_ENDT LOA_RQST_LVE_ENDT LOA_ACTL_RTW_DT 6X ACTL_LVE_ENDT BRANCH COV_TYPE COV_STAT_RSN COV_STAT TOPS_CODE LOA_STATUS LOA_CAT BEN_BGDT_DT LVE_RSN_CD LVE_SUB_RSN_CD LVE_STAT_CD LVE_TYPE_CD MSG

and for the INTERMITTEN_FMLA_RETURN_TO_WORK sheet, it'd be:

EE_ID TRANS_TIME UNIQ_ID SSN TRANS_DATE FULL_NAME 6X RQST_LVE_BGDT LOA_RQST_LVE_BGDT 6X RQST_LVE_ENDT LOA_RQST_LVE_ENDT LOA_ACTL_RTW_DT 6X ACTL_LVE_ENDT BRANCH COV_TYPE COV_STAT_RSN COV_STAT TOPS_CODE LOA_STATUS LOA_CAT BEN_BGDT_DT LVE_RSN_CD LVE_SUB_RSN_CD LVE_STAT_CD LVE_TYPE_CD MSG

3. Most of the time, there'll be no employee in NOACTION or MULT_RTW sheet.

Trowa, I just want to have an error-free macro. Like if I have to show how this macro will going to work, it must work perfectly. I hope you'll understand what I'm trying to say.

Thanks for all your time and effort, really appreciate it!!

Regards
Kamal Hasan

Answer 23 / 29

Kamal Hassan
Jul 14, 2012 at 05:51 AM

Hi Trowa

Have not forgotten you, it's just that I have changed my job now.

Will ask you for more help in coming time.

Thanks for all your help...U r really a fantastic person :-)

Best Regards

Kamal Hassan

Answer 24 / 29

kamaal_hasan2005
Nov 27, 2013 at 01:47 AM

Hello Trowa

How are you??? Long time to talk to you !!

Hope you are doing great..!!

Need your help once again...It's a simple one :-)

If I want to update a different series of serial nos. in a particular column, how would I go about it. For example, its written 'P1' in cell A2 and 'D1' in A3. Now I want to get the following series:
P2
D2
P3
D3
P4
D4

Looking forward to your response.

Kind regards,
Kamal Hassan

Didn't find the answer you are looking for?

Ask a question

Answer 25 / 29

kamaal_hasan2005
Nov 28, 2013 at 01:14 PM

It's so nice to heard back from a genius like you, Trowa :-)

Thanks a ton for your help...

As you know I'm a beginner in terms of using MS Excel, just need to know if I need the serial nos. in following order:

P1
P2
P3
D1
P4
P5
P6
D2

I did some R&D using the manner provided by you in your response, however, not getting the desired results.

Actually, I'm working on a office project that needs this kind of serial nos.

With best regards
Kamal Hassan

Answer 26 / 29

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 3, 2013 at 11:59 AM

You are very welcome Kamal!

For this a macro is needed, here you go:

Sub RunMe()
Dim lRow, xP, xD As Integer
lRow = Range("A1").End(xlDown).Row
xP = 1
xD = 1
For Each cell In Range("A1:A" & lRow)
    If cell.Value = "P" Then
        cell.Value = "P" & xP
        xP = xP + 1
    End If
    
    If cell.Value = "D" Then
        cell.Value = "D" & xD
        xD = xD + 1
    End If
Next cell
End Sub

Best regards,
Trowa

Answer 27 / 29

kamaal_hasan2005
Dec 10, 2013 at 12:10 AM

Morning Trowa,

Hope you are doing great !!

Thanks for your assistance in sorting out my last concern. Another one for the genius:

I have workbook having around 17-18 sheets where in my first sheet named Master Index contains the hyperlinks to rest of the sheets.

Now, I want to hide all the sheets except the first one and access the sheets via hyperlinks. I tried doing that but when I click on the hyperlink, none of the sheets were appearing.

Names of all the sheets are as follows:

Master Index
DR_DO_DFO_OSD
AR_AO
AO
SO_AAO_Accountant
Office Asstt.
UDC-I
UDC-II
LDC-I
LDC-II
LDC-III
PA
Asstt. Lib.
Prof. Asstt
Semi Prof. Asstt
Sr. Lib. Attdtt.
Lib. Attdtt.
Asstt. Exec. Engg.

Most importantly, I also want all the sheets to be password protected so that no can make any change(s) in any of the sheets.

Thanks & Regards
Kamal Hassan

Answer 28 / 29

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 10, 2013 at 12:05 PM

Hi Kamal,

Forget the hyperlinks.
As example I placed the first 2 sheet names in A2 and A3 of the Master Index sheet.
Then right-click the Master Index sheet tab and select view code.
Here is the code that needs to go there:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A2")) Is Nothing Then
    Sheets(Target.Value).Visible = True
    Sheets(Target.Value).Activate

ElseIf Not Intersect(Target, Range("A3")) Is Nothing Then
    Sheets(Target.Value).Visible = True
    Sheets(Target.Value).Activate

End If
End Sub

Then to hide each sheet again use this bit of code:

Private Sub Worksheet_Deactivate()
Sheets("DR_DO_DFO_OSD").Visible = False
End Sub

So right-click on DR_DO_DFO_OSD sheet tab and select view code.

Password protection is nothing special but differs per Excel version. Best to do is do a google search: How to protect my sheet Excel 20**.

Have to go now, talk to you later,
Trowa

Answer 29 / 29

kamaal_hasan2005
Dec 11, 2013 at 04:57 AM

Hello Trowa

I've pasted the below code in Master Index sheet and changed the cell range to G6 and G7. However, the code is not running and giving an error in the line " Sheets(Target.Value).Visible = True".

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("G6")) Is Nothing Then
Sheets(Target.Value).Visible = True
Sheets(Target.Value).Activate

ElseIf Not Intersect(Target, Range("G7")) Is Nothing Then
Sheets(Target.Value).Visible = True
Sheets(Target.Value).Activate

End If
End Sub

Further, I have also copied the below code in first sheet i.e. DR_DO_DFO_OSD and it's showing hidden now. So do I need to paste the code in all other sheets after changing its name?

Kind regards,
Kamal Hassan

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 12, 2013 at 10:29 AM

Hi Kamal,

If "Sheets(Target.Value).Visible = True" is giving an error then please check that the value entered in the cell is exactly the same as the sheet name. Apparently Excel can't find a sheet with the same name as the cell thus giving an error.

And the second part of the code indeed needs to go into every sheet you want to hide again.

The idea was to double-click a cell with a sheet name in it to make it appear and to activate it (goto sheet). Once the sheet is deactivated (another sheet is selected), the sheet will be hidden again.

Sorry for the bad explanation before, I was in kind of a hurry.

Best regards,
Trowa

Similar discussions

Confirm your identity with phone or computer

Excel - To generate unique ID

How to generate a STATIC unique identifier for each Excel row

Facebook message show confirm your identity

Autogenerate alphanumeric ID numbers in Excel?

Similar discussions

Subscribe To Our Newsletter!