EPTExcel Pros Please Help! - Page 3
| Blogs > Golgotha |
|
josemb40
Peru611 Posts
| ||
|
Glacierz
United States1244 Posts
http://www.wallst-training.com/WST_Excel_Shortcuts.pdf It will make you look like a pro if you ever do a live demo of anything excel related. The goal here is to do everything without using a mouse. | ||
|
bellweather
United States404 Posts
Sub combineDuplicates() For i = 2 to ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row If Cells(i,2).Value = Cells(i-1,2).Value And Cells(i,3).Value = Cells(i-1,3).Value And Cells(i,4).Value=Cells(i-1,4).Value Then For j = 4 to ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column Cells(i-1,j) = Cells(i-1,j).Value & ";" & Cells(i,j).Value Next j Cells(i,j).EntireRow.Delete i = i-1 Next i End Sub This is assuming column 2,3,4 are your name identifiers and anything after are things that should be concatenated. edit: and yes I know this code is weak super lame for you VBA'ers out there, but deal with it | ||
|
endy
Switzerland8970 Posts
Select your 6000 as well as the columns name, cell, address. Click on the Excel tab "Data". Then click on the icon "Remove Duplicates". And voilà. | ||
|
infinity21
Canada6683 Posts
Only way I can think of right now is 1) sort 2) concatenate the first and last name into a column at the end, say Z 3) for row i, declare a dynamic array of size (countif(Z: Z ,Zi), original # of columns) 4) have a function that looks at each entry starting from row i to row i + countif(Z: Z, Zi) -1 and appends to the dynamic array if it isn't in the array already 5) paste the dynamic array with a function that automatically inserts a separator between each entry and doesn't put in blanks 6) i=i+countif(Z: Z, Zi) and repeat | ||
|
MisterD
Germany1338 Posts
On December 16 2011 04:49 infinity21 wrote: 1) Sort 2) Remove duplicates 3) Add a column with the function =COUNTIF(A:A,A2) where 2 is the row number 4) Filter for the new column for values >=2 If there's entries for 3+ phone numbers, you're going to have to append the numbers to a different column If you're lucky and there's only a few rows with >=2 phone #s, then you can do it manually without having to code in VBA. I don't think someone who doesn't know VBA would know SQL lol well -- i do? the "new gen" university software developers around here are all java grown. with the advancement of mac and linux on personal use computers, there's no point in even trying to use a microsoft language during education. SQL however is pretty much the first thing in any database lecture. so there's a whole bunch of people who are this way. though you are probably correct with your assumption "out in the market", where microsoft languages are a lot more popular due to their legacy. | ||
|
infinity21
Canada6683 Posts
On December 17 2011 02:32 MisterD wrote: well -- i do? the "new gen" university software developers around here are all java grown. with the advancement of mac and linux on personal use computers, there's no point in even trying to use a microsoft language during education. SQL however is pretty much the first thing in any database lecture. so there's a whole bunch of people who are this way. though you are probably correct with your assumption "out in the market", where microsoft languages are a lot more popular due to their legacy. Yeah I was referring more to the workplace. People who knows SQL are business analysts or database administrators (generalizing here obv) so they usually have some programming skills like Java, C++, etc. And imo if you know how to program in either one of those two, you can pick up VBA in a couple of hours which has been the case for me at least. Not to say that there are zero jobs that require SQL and no VBA, but those are more of the exceptions. | ||
| ||
