SEARCH

The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

(Solved) - how to merge same names into one in microsoft excel

(33 posts)
  • Started 2 years ago by amrinderminhas
  • Latest reply from nosparks
  • Topic Viewed 3045 times

Enthusiast
Enthusiast
Posts: 566

@amrinderminhas,

Nosparks has provided a solution to convert the data from the format you gave us initially to the format you asked for. I realize you may not know how to implement the solution. The steps to implement his solution are:

First, open the worksheet containing your information (so you can save the macro code in that worksheet)
1. Create a macro and give it a name (such as "ReformatData") and a ctrl-letter key combination (such as ctrl-r, for ReformatData)
2. Select cell A1 by clicking in it (to give the macro a line of code)
3. Stop the macro recording.
4. Edit the macro (Tools->Macro->(Click macro name)->Edit)
5. Delete the line that says: Range("A1").Select
6. Paste the code from nospark's post into the same line (If you named your macro "ReformatData", don't include the first line of nosparks macro, the "Sub ReformatData()" line:
7. Close the macro editor by clicking the "X" in the upper right. The macro will be saved automatically when closed.
8. At this point, you should be able to run your macro. Hit Ctrl-r to generate your results.

If this is unclear or doesn't work for you, let us know so we can clear it up for you.

Posted 2 years ago
Top
 
amrinderminhas
Posts: 59

In the imported spreadsheet the format is same as i have shown in the image earlier where the names like jack , ben etc. has been written....
Its just i thought may be u or someone could solve this problem which i just linked in 2nd image (2 posts back) then i could copy paste all the names (which i have in my soft copy) on left side of blue block nd purpose will be solved..

all the attendance in red colour are of one person
All the attendance in green colour is of another person
similarly in blue colour of 3rd person.
So its easy for me to copy paste in the end all the names on the left side of blue block... no issues...
Just give me the solution to convert the above picture'red block into blue block solution by any "formula"...coz its just 1 % of the dates i have shown u all....

Posted 2 years ago
Top
 
amrinderminhas
Posts: 59

@enthusiast.. I ll try to do the steps u have written along with the code nosparks mentioned then ll see what the outcome will be... Surely will inform you all in any case...

Posted 2 years ago
Top
 
Enthusiast
Enthusiast
Posts: 566

@amrinderminhas,

Give the macro a try on your original data. That way you won't have to manually do anything.

Keep us posted.

Posted 2 years ago
Top
 
nosparks
Posts: 148

@amrinderminhas

If you are having problems you could send your .cvs file to NoSparks at gmail dot com if you would like. But I think it best to somehow get it through the forum as it is then available to all those interested.

NoSparks

Posted 2 years ago
Top
 
amrinderminhas
Posts: 59

@nosparks.. I appreciate ur concern.. and i m impressed with the way u are helping all. Let me apply ur macro (though i havnt done it ever).. if not successful then i ll mail u my csv file..

Posted 2 years ago
Top
 
amrinderminhas
Posts: 59

@nosparks... ooopsss..i think its not so easy for me.. i have sent a csv file to you.. Kindly after doing it.. do give me the steps of how you done that....

Posted 2 years ago
Top
 
Enthusiast
Enthusiast
Posts: 566

@nosparks,

In your code, where you copy/paste the information to Sheet2, you need to ensure the cursor is in position A1 on sheet2 before the paste, or your code fails.

This:

'copy data to sheet 2
Range("A1:D" & LastRow & "").Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select

Should look like this:

'copy data to sheet 2
Range("A1:D" & LastRow & "").Copy
Sheets("Sheet2").Select
Range("A1").Select    ' Make sure cursor is positioned in cell A1 on Sheet2
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select

Also, in the interest of making the information easier to read, you can add the following to the end of the code, to widen all cells so the results display without "###"s:


'Resize Columns in worksheet to show all data
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

Posted 2 years ago
Top
 
nosparks
Posts: 148

Thanks Enthusiast, I'll rectify that.

Received the .cvs file from amrinder and it's not quite as illustrated at start of topic. Don't know if it's regional settings or what but some things don't make sense on my computer. I took screen shot of what I see and sent it back with some questions and asking for permission to post it here for others to see and help with.

Posted 2 years ago
Top
 
amrinderminhas
Posts: 59

Oh my God!!!!!!!!!!!!!!!!!!!! I have applied ur code that nosparks have written 4-5 days back..... and enthusiast helps me to apply that step by step today... and i cant believe my eyes its magic!!!!!!. Its what i wanted... exactly sir exactly......I am thrilled.
R u people of this world!!!!!... I cant even understand a little of the code nosparks has written, though i have touched a lill bit of C++ programming, for 2 months in my graduation...... How could u learned such programming. Kindly tell me what actually is above code.. I mean what kind of programming it is called and how much time it takes to learn and master this...
But I salute you. Its as perfect as i can never ever think to get it...
Kindly be in touch with me. I am a lecturer in Mechanical Engineering working in a University. I do have fascinating ideas which i have never thought before today to get it into real shape coz of my limitation in programming.
I ll definitely like to share these with u in coming days so that anybody else could also take advantage of the geniuses here present.
kIndly tell me the name of this programming u hv done and the time req. to aquire such skills.
Its been my pleasure to have a contact with such intelligent and generous people out there!!!!
Thanx buddy thanx to all and especially nosparks and enthusiast.

Accepted Answer · Posted 2 years ago
Top
 
amrinderminhas
Posts: 59

thanx to u all once again... I am pleased and thankful to have such helpful persons around here. Thanx again!!!!!!!!!!!!!!!!!!

Posted 2 years ago
Top
 
Enthusiast
Enthusiast
Posts: 566

The programming is done in VBA (Visual Basic for Applications). This is the background coding used by all of the Microsoft Office programs to enable us lowly users to perform tasks not inherent in the base programs. A search on "VBA tutorials" should get you started if you are interested in getting into learning it. Remember to ask questions when necessary.

You are very welcome! We are glad you have gotten this sorted out. The kudos go to nosparks for his coding :)

If at any time you wish to discuss future projects, post here and someone will be happy to help.

Posted 2 years ago
Top
 
nosparks
Posts: 148

Won't be posting screen shot of what OP sent me as I was requested not to. Suffice it to say his original post was very indicative of where things start but actual first column is student numbers as opposed to first names and the file I received had manually added data starting about 1900 rows down. When the OP applied the posted suggestions to the actual imported data all went well. Ya gotta like that.

Posted 2 years ago
Top
 



Topic Closed

This topic has been closed to new replies.

Enter Your Email Here to Get Access for Free:

Go check your email!