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

amrinderminhas
Posts: 59

Dear All,
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)

Posted 2 years ago
Top
 
Xhi
Xhi
Posts: 6298

Bump

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

Hi Amrinder

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

=B3&C3&D3

and copy this down to the end of your list. You will end up with data that looks like this

Jack410891
Jack410895
Jack410905
Jack410911
Jack410915
Rocky410891
Rocky410895
Rocky410905
Rocky410911
Rocky410915
Roger410891
Roger410895
Roger410905
Roger410911
Roger410915
Ben410891
Ben410895
Ben410905
Ben410911
Ben410915

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;

=IFERROR(VLOOKUP($G4&H$1&H$2,$A$1:$E$22,5,FALSE),"")

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)

Posted 2 years ago
Top
 
nosparks
Posts: 148

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

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

@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.

Posted 2 years ago
Top
 
Enthusiast
Enthusiast
Posts: 566

We may be able to accomplish this by generating an array, but I haven't time to devote at the moment. Give me a day or two and I will look into it.

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

@Enthusiast - do you mean from VBA?

Posted 2 years ago
Top
 
DanFlak
Posts: 2

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.

Posted 2 years ago
Top
 
Enthusiast
Enthusiast
Posts: 566

@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?

Posted 2 years ago
Top
 
nosparks
Posts: 148

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.

Sub ReformatData()

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
Sheets("Sheet2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select

'remove blank row 2
Range("B2").EntireRow.Delete
'last row is now 1 less
LastRow = LastRow - 1

'add new column to make date/period distinct
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'add header
Range("B1").Select
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)
Next cel

'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.Copy
Range("L3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("A1").Select

'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)
End If

For Each prdate In PeriodDates
'check date
If prdate.Value = nam.Offset(0, 2).Value Then
'check period
If prdate.Offset(1, 0) = nam.Offset(0, 3) Then
'put in absent or present
Cells(RowToUse, prdate.Column) = nam.Offset(0, 4).Value
End If
End If
Next prdate

Next nam

End Sub

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

@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.

Posted 2 years ago
Top
 
nosparks
Posts: 148

@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.

NoSparks

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

Hi Don
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.

Moree

Posted 2 years ago
Top
 
Enthusiast
Enthusiast
Posts: 566

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.

Posted 2 years ago
Top
 
amrinderminhas
Posts: 59

Dear all,
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

Posted 2 years ago
Top
 
nosparks
Posts: 148

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.

NoSparks

Posted 2 years ago
Top
 
amrinderminhas
Posts: 59

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........

Posted 2 years ago
Top
 
nosparks
Posts: 148

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

Posted 2 years ago
Top
 
amrinderminhas
Posts: 59

@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...

Posted 2 years ago
Top
 
nosparks
Posts: 148

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!
Thanks folks.

NoSparks

Posted 2 years ago
Top
 



Topic Closed

This topic has been closed to new replies.