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!
This topic has been closed to new replies.