I have a problem regarding Excel shown in following problem... The red block is the problem (imported data) and blue block is the requirement what i need finally...
I know how to transpose by rightclicking then paste special then transpose, but i dont know how to merge all same names into one in the way done in blue block.
Though there is filter way in which i can filter diff names and the copy paste, but friends, this excel shown to you is just a part of whole problem. Actually there are 95 names and each name repeated in 5 -6 times. So Can you plz give me a solution to this problem(red block) which require very less time to get the output(as shown in blue block)
(Solved) - how to merge same names into one in microsoft excel(33 posts)
To completely automate this you would probably need a fairly complex macro. To partially automate this you can do the following;
Add a new column A before your input data that will be reference data. In the first cell of the new column put in this formula
and copy this down to the end of your list. You will end up with data that looks like this
Where you want your results you will have to manually add in the column names and row names just as you have it in your screen shot. Then in Cell next to the first name (H4 - because we added the extra column) put in this formula;
copy this across all the columns you have and down all the rows. This will populate those sells with "Absent" or "Present"
Give this a try until someone with some time on their hands can give you a completely automated solution (this would take someone at least several hours - if not days to do)
Amrinder, when you say "Actually there are 95 names and each name repeated in 5 -6 times" is that 95 different names each repeated 5 - 6 times to produce a table of 500 to 600 lines pertaining to 5 or 6 date/period times or is it 18 or 20 different names being repeated for each date/period time?
My first instinct reading your post would be to import the original data into MS Access, use a distinct query and create a report, but that's not helping with Excel. Interesting problem, I'll be keeping an eye on this topic.
@nosparks - of course you're right - this would seem to best suit a traditional database approach. However, not everyone has access (pun intended) to a DBMS and if they do, unless they have some experience in database concepts and functions, it would be an extremely steep learning curve to get even a basic solution going. This in contrast to Excel where most people can grasp the basics fairly quickly and for more complex Excel problems can apply the solutions fairly readily if shown how.
We'll see how this progresses if the OP returns and maybe he will be open to an Access solution.
There is a non-vba way, it involves using a helper clolumn. I can see by the ribbon that you have Excel 2007 or 2010. This means you have tables. I convertred your range into a table (Table1) since it is a lot easier to work with tables than formulas. Table names are easier to read. Also if you highlight the rows and right click and select Delete -> Table Rows, it "remembers" any formulas. When you copy and paste data, they come back.
I don't know what you meant by "imported" data. I assumed copy and paste, but generally, data imported via the Data Ribbon will come in as a table.
First I added an extra column so the names in the matrix starte in column G.
I added a helper colum in Column E with the header Composite and the formula: =[FirstName]&"-"&[Date]&"-"&[Period]. This is basically a composite "key" for the record.
Then I filled out the matrix in Columns G:L. Cell H3 contains the formula: =INDEX(Table1,MATCH($G3&"-"&H$1&"-"&H$2,Table1[Composite],0),4)
You will have to copy this formula for as many Name, Date and Period combination cells as you have in the matrix.
Edit: Oops I can see someone already beat me to this solution.
@moreeg, (Love the avatar) Yes, I think it can be done with a macro, even without generating an array. However, I won't expend time on it if your suggestion is accepted.
@amrinderminhas, Is the data always uniform? For instance, Is there information for each day for each student, or can information be missing in places?
Created sheet1 as Original Poster has pictured. Created the following VBA in a module. Ran the macro and the desired format and merge is created on sheet2. All progressions of the routine are left on sheet2.
Dim LastRow As Long
Dim LastCol As Long
Dim DataDates As Range
Dim AllNames As Range
Dim PeriodDates As Range
Dim cel As Range
Dim nam As Range
Dim prdate As Range
Dim RowToUse As Long
Dim MergedName As String
'find the last data row using the date column
LastRow = Range("B65536").End(xlUp).Row
'copy data to sheet 2
Range("A1:D" & LastRow & "").Copy
Application.CutCopyMode = False
'remove blank row 2
'last row is now 1 less
LastRow = LastRow - 1
'add new column to make date/period distinct
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.FormulaR1C1 = "DandP"
'combine date and period for distinction in new col B
'the range of dates being worked with
Set DataDates = Range("C2:C" & LastRow & "")
For Each cel In DataDates
cel.Offset(0, -1) = cel & cel.Offset(0, 1)
'create distinct date/period table starting at G1
Range("B1:D" & LastRow & "").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns _
("B:B"), CopyToRange:=Range("G1"), Unique:=True
'transpose copy portion of distinct date/periods for date/period layout
Range("H2:I" & Range("I1").End(xlDown).Row & "").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
Application.CutCopyMode = False
'setup for merged names in column K
LastRow = Cells(1, 1).End(xlDown).Row
LastCol = Cells(3, 12).End(xlToRight).Column
Set AllNames = Range("A2:A" & LastRow & "")
Set PeriodDates = Range(Cells(3, 12), Cells(3, LastCol))
'setup the first name of the merged names in column K
RowToUse = 4 'the row in col K 1 before where the names start
MergedName = Cells(RowToUse, 11).Value
For Each nam In AllNames
'if the name doesn't match put the name you got into the next cell
If nam <> MergedName Then
RowToUse = RowToUse + 1
Cells(RowToUse, 11) = nam
MergedName = Cells(RowToUse, 11)
For Each prdate In PeriodDates
If prdate.Value = nam.Offset(0, 2).Value Then
If prdate.Offset(1, 0) = nam.Offset(0, 3) Then
'put in absent or present
Cells(RowToUse, prdate.Column) = nam.Offset(0, 4).Value
@Enthusiast - I've been thinking about a macro as well - my first approach would be to take each of the first 3 columns and dedupe them in another sheet then create the shell of the table and then do something similar to my non-macro solution to make all the associations. I don;t think amrinder is coming back though so we'll never know what it is he really needed.
@DanFlak - welcome to HTG - it's always good to see a new Excel maven chipping in especially a "match/index"er. The world, it seems, is split between "vlookup"ers and 'match/index"ers and I'm in firmly the first camp. It'd be good to have someone from the other side here as well. Enthusiast, I think, is the Excel intellectual - always goes for the macro solution.
@moreeg - suspect your right about amrinder not returning. Too bad, I thought it would be (relatively) easy to get a "click-click-done" solution.
This is more of a BUMP than anything else although I will admit without more insite of where the OP starts and really wants to go it's hard to establish the route to take.
The "(relatively)" is the rub. What it usually takes is a fair commitment from the OP to work with us on the solution to get to the solution. Here is a case in point where Enthusiast put in a lot of work to get to a solution but the OP was responsive and committed and the result was a very successful conclusion; the ops final words are "it completes the task in a fraction of the time it would have taken me to do it!" and is the best accolade a developer ever gets.
Unfortunately this example is more of an exception than the rule one would like it to be.
Good to see more members breaking ground with us moreeg :)
Thanks nosparks and Danflak! Good to see you in on this one. Too bad OP hasn't returned. I only hope they received a response from somewhere that helped.
I respect all ur solutions to the problem but as "nosparks"guessed i m not so expert in DBMS in MSAccess.
Secondly, there are 95 names. Actually its the data of the students. So if in a day there are 2 periods so in 5 days there will be 10 periods. It means each name is repeated 10 times. so u can guess 95 names will be repeated for 95*10=950 times. Now if i have to gather the report of the students for one full session(i.e.. say..50 days) so it will go a long way.
I have iphone 4 and installed an app. "teacherpal" where u can record the attendance but when its imported in .csv format it generates the result in the way shown above in red block picture.
I do have done various here & there formulas and always solved the purpose bt finally really caught.
I appreciate all of ur concern, bt kindly give me an easy way to do this.
sorry friends was a bit busy bt it ll not happen again... kindly help me on this
Amrinder, thanks for coming back. What you are in need of from your Excel program is not really very difficult to achieve but we need to know exactly what you are starting with and what you need to end up with. I doubt your 95 students all have unique names so please show us what your .csv file is containing. Help us help you and I'm sure you will have a solution in very short order.
Hello to all... I have reduced the problem a little... If someone of u can easily give the solution as in blue block from the red block problem.. It will solve the purpose........
amrinder I can see you have copied some of your imported information into a new workbook but.... there are no student names or ids. Need to know how this information is on the imported spreadsheet. I suspect privacy issues and such make that you don't want to post actual info with real names or things like that but there is a need to know exactly, in layout at least, what it is that you are starting with. If possible would you please repost. Thanks.
@nosparks.. sorry, i dnt know how to attach .csv file here... If u could tell me that, then i could upload the entire excel sheet for u to understand and work on that excel in realtime...
Sorry, I don't know that and I'm not sure that you would really want it to be forever available to the world. Perhaps someone else could help out with this situation. PLEASE!
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.
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....
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.. 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..
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.
'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
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.
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.
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.
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.
This topic has been closed to new replies.