Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

Brain In A Jar: Apr 21, 2008

Old James posted:

Try Qlikview (they have a free version which you can download from their site).

You can use the built in wizard to load the data instead of coding with their twist on SQL. Once the data is loaded, choose the fields you want to filter on and create a table object for the fields you want to export. The table object will have an option to export back to Excel based on whatever matches your filters. If the result is more than 65535 rows, it automatically changes the output to CSV.

This seems pretty awesome, thanks! Running some tests with it now, but from the looks of it this might be the answer.

# ? Mar 19, 2014 04:12

Adbot: ADBOT LOVES YOU

# ? Apr 28, 2024 17:46

Ragingsheep: Nov 7, 2009

Is it generally more efficient to use a Pivot table or to replicate the table using formulas like countifs, sumifs, index/match?

# ? Mar 19, 2014 04:35

Darth TNT: Sep 20, 2013

Ragingsheep posted:

Is it generally more efficient to use a Pivot table or to replicate the table using formulas like countifs, sumifs, index/match?

Depends on how much it is, how the data is formatted and what/how you want the endresult to look like.
Pivot tables are super fast and easy to use, but in my experience aren't always flexible enough to allow me the set up I want. (Main problem being % on totals and comparisons between columns/rows)
Whenever that happens I tend to use the pivot table to get all the unique values in the list and then use the formulas to create my own table.

# ? Mar 19, 2014 10:55

Turkeybone: Dec 9, 2006

This seems trivially easy, but can someone tell me the code for "for each workbook in this folder, pull out the value from A1?" (I'll be doing more with it, but my brain just seizes at the workbook level apparently).

# ? Mar 19, 2014 13:08

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Turkeybone posted:

This seems trivially easy, but can someone tell me the code for "for each workbook in this folder, pull out the value from A1?" (I'll be doing more with it, but my brain just seizes at the workbook level apparently).

Modified from http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba
Haven't tested the changes, but it should work.

code:

Sub LoopThroughFiles()
    Dim file As Variant
    Dim wb as Workbook
    Dim ws as worksheet
    Dim CELL as range

    set CELL = thisworkbook.sheets(1).range("A1")

    file = Dir("c:\testfolder\")
    While (file <> "")
        If InStr(file, ".xls") > 0 Then
            set wb = workbooks.open file
            For Each ws in wb.worksheets
                cell.value = ws.range("A1").value
                set cell = cell.offset(1,0)
            Next ws
            wb.close savechanges:=false
        End If
       file = Dir
    Wend
End Sub

# ? Mar 19, 2014 14:58

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Dir accepts a pattern too, so you can do the following instead (Dir will then list only Excel files, so you don't have to check inside the loop):

code:

Sub LoopThroughFiles()
    Dim file As Variant
    Dim wb as Workbook
    Dim ws as worksheet
    Dim CELL as range

    set CELL = thisworkbook.sheets(1).range("A1")

    file = Dir("c:\testfolder\*.xls")
    While (file <> "")
       set wb = workbooks.open file
       For Each ws in wb.worksheets
           cell.value = ws.range("A1").value
           set cell = cell.offset(1,0)
       Next ws
       wb.close savechanges:=false
       file = Dir
    Wend
End Sub

If you think the syntax for Dir is kinda dumb and awkward, you can add the reference to the Microsoft Scripting Runtime and use the FileSystemObject and related classes instead. This way you can use the "for each" iteration over both files and sheets in the files.

code:

Sub LoopThroughFilesFSO()
    Dim theFile As file
    Dim ws As Worksheet
    Dim CELL As Range
    
    Dim fso As New FileSystemObject
    
    Set CELL = ThisWorkbook.Sheets(1).Range("A1")
    For Each theFile In fso.GetFolder("c:\testfolder\").Files
        If fso.GetExtensionName(theFile.Name) = "xls" Or fso.GetExtensionName(theFile.Name) = "xlsx" Then 'or whatever
            With Workbooks.Open(theFile.Path)
                For Each ws In .Worksheets
                    CELL.Value = ws.Range("A1").Value
                    Set CELL = CELL.Offset(1, 0)
                Next ws
                .Close savechanges:=False
            End With
        End If
    Next theFile
End Sub

# ? Mar 20, 2014 00:29

icantfindaname: Jul 1, 2008

I have a VBA script to go through a list of ages in various formats and convert them to raw numbers, and it works properly except in the case where the text is "10 and up".

code:

Sub Parse()
    
    Dim str1 As String
    Dim pos1 As Integer
    Dim pos2 As Integer
    
    For i = 1 To ActiveSheet.UsedRange.Rows.Count
        On Error Resume Next
        
        str1 = VBA.Strings.Trim(Cells(i, 1).Value)
        
        If VBA.Strings.InStr(str1, "At least") <> 0 Then
            pos1 = VBA.Strings.InStr(str1, "but")
            pos2 = VBA.Strings.InStr(str1, "At least") + 8
            Cells(i, 2).Value = VBA.Strings.Mid(str1, pos2, pos1 - pos2 - 1)
            
            If VBA.Strings.InStr(str1, "y") <> 0 Then
                pos1 = VBA.Strings.InStr(str1, "y")
                pos2 = VBA.Strings.InStr(str1, "than") + 5
                Cells(i, 3).Value = VBA.Strings.Mid(str1, pos2, pos1 - pos2) + 1
            Else
                pos2 = VBA.Strings.InStr(str1, "than") + 4
                Cells(i, 3).Value = VBA.Strings.Right(str1, VBA.Strings.Len(str1) - pos2)
            End If
        ElseIf VBA.Strings.InStr(str1, "and up") <> 0 Then
            pos1 = VBA.Strings.InStr(str1, "and up")
            Cells(i, 2).Value = VBA.Strings.Left(str1, pos1 - 1)
            Cells(i, 3).Value = 99
        End If
    Next
End Sub

This part is what's causing the problem

code:

...
        ElseIf VBA.Strings.InStr(str1, "and up") <> 0 Then
            pos1 = VBA.Strings.InStr(str1, "and up")
            Cells(i, 2).Value = VBA.Strings.Left(str1, pos1 - 1)
            Cells(i, 3).Value = 99
...

For some reason Excel is automatically converting '10' into '1/10/1900 12:00:00 AM'. How do I get this to stop?

# ? Mar 22, 2014 06:43

Brain In A Jar: Apr 21, 2008

Try using '10 in the cell containing the actual value? ' causes Excel to treat cell content as literal instead of acting as a date.

# ? Mar 22, 2014 12:45

sofokles: Feb 7, 2004; Fuck this

icantfindaname posted:

I have a VBA script to go through a list of ages in various formats and convert them to raw numbers, and it works properly except in the case where the text is "10 and up".
code:
...
        ElseIf VBA.Strings.InStr(str1, "and up") <> 0 Then
            pos1 = VBA.Strings.InStr(str1, "and up")
            Cells(i, 2).Value = VBA.Strings.Left(str1, pos1 - 1)
            Cells(i, 3).Value = 99
...
For some reason Excel is automatically converting '10' into '1/10/1900 12:00:00 AM'. How do I get this to stop?

Have you tried using Cells(i,2).value2 = CInt(VBA.Strings.Left(str1, pos1 - 1))

# ? Mar 22, 2014 16:24

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

icantfindaname posted:

This part is what's causing the problem
code:
...
        ElseIf VBA.Strings.InStr(str1, "and up") <> 0 Then
            pos1 = VBA.Strings.InStr(str1, "and up")
            Cells(i, 2).Value = VBA.Strings.Left(str1, pos1 - 1)
            Cells(i, 3).Value = 99
...
For some reason Excel is automatically converting '10' into '1/10/1900 12:00:00 AM'. How do I get this to stop?

Excel is not "converting", it is displaying in the wrong number format (dates are really just numbers displayed in a fancy way, starting at zero being the beginning of 1900). Most likely the cell used to be formatted as a date, or perhaps it has dates somewhere around it and Excel has decided to infer the number format (e.g. if you put a bunch of dates in a column, it will automatically assume the next row down is also a date). You can just change the formatting of the cell.

You probably don't want to put the single quote, because it will then treat all your numbers as strings (i.e. can't do arithmetic, ordering is wrong, etc).

# ? Mar 22, 2014 17:23

icantfindaname: Jul 1, 2008

Alright I fixed it by using Cells.Value = Val(VBA.Strings.Left(...))

Is there any reason to use CInt over Val or vice versa?

Also, how would I search through a particular row to find a string, and then return the column of the first cell containing that string?

# ? Mar 22, 2014 20:36

sofokles: Feb 7, 2004; Fuck this

icantfindaname posted:

Alright I fixed it by using Cells.Value = Val(VBA.Strings.Left(...))

Is there any reason to use CInt over Val or vice versa?

Also, how would I search through a particular row to find a string, and then return the column of the first cell containing that string?

CInt and Val both return numbers, but CInt only natural ones. Doesn't matter, the effect is that you tell Excel that these things are numbers, not strings that it's up to Excels wisdom to figure out how to parse and present. Strings.Left() returns a string and not a raw number.

Also if you use cells.value2 = , instead of value = , your numeric value will be preserved even if Excel decides to present it as a date.

For finding a value learn xlFind : http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.15).aspx

nice tut here : http://www.vbforums.com/showthread.php?634644-Excel-Find-Method-in-Excel-VBA-(Any-version-of-Excel)

sofokles fucked around with this message at 18:36 on Mar 23, 2014

# ? Mar 23, 2014 18:29

Turkeybone: Dec 9, 2006

ShimaTetsuo posted:

Dir accepts a pattern too, so you can do the following instead (Dir will then list only Excel files, so you don't have to check inside the loop):

code:

Sub LoopThroughFiles()
    Dim file As Variant
    Dim wb as Workbook
    Dim ws as worksheet
    Dim CELL as range

    set CELL = thisworkbook.sheets(1).range("A1")

    file = Dir("c:\testfolder\*.xls")
    While (file <> "")
       set wb = workbooks.open file
       For Each ws in wb.worksheets
           cell.value = ws.range("A1").value
           set cell = cell.offset(1,0)
       Next ws
       wb.close savechanges:=false
       file = Dir
    Wend
End Sub

code:

Sub LoopThroughFilesFSO()
    Dim theFile As file
    Dim ws As Worksheet
    Dim CELL As Range
    
    Dim fso As New FileSystemObject
    
    Set CELL = ThisWorkbook.Sheets(1).Range("A1")
    For Each theFile In fso.GetFolder("c:\testfolder\").Files
        If fso.GetExtensionName(theFile.Name) = "xls" Or fso.GetExtensionName(theFile.Name) = "xlsx" Then 'or whatever
            With Workbooks.Open(theFile.Path)
                For Each ws In .Worksheets
                    CELL.Value = ws.Range("A1").Value
                    Set CELL = CELL.Offset(1, 0)
                Next ws
                .Close savechanges:=False
            End With
        End If
    Next theFile
End Sub

So I went about this a slightly different way, but this might answer my follow-up question (and final big step in some report automation I've been working on). Basically I now have a folder of reports to send to sales reps and cc to their managers.. I now basically just need to attach the appropriately named reports to said emails. The filenames have their unique ID numbers, so could I simply do something like:
For each file in folder
if file.name like "12345" (their ID number)
then attachments.add file.path

Obvi that's some rough pseudocode, and I *think* outmail.attachments takes a path. If there is a set order that files are in a folder (alphabetical), then I could just do it 1 to 1 without an if, right? It's only ~30 files, so it's not really a huge issue searching it out I don't think.

I'd appreciate any criticism to this logic, thanks.

Edit: I used instr(filename, idnumber) to check and I got it to work exactly how I wanted, so yay! Thanks for letting me talk it out to you, thread.

Turkeybone fucked around with this message at 18:48 on Mar 25, 2014

# ? Mar 25, 2014 18:02

me your dad: Jul 25, 2006

I have a workbook with data in columns A, B, and C. Column B contains a series of dates.

There are a few thousand rows containing approximately 10 or so unique dates.

Example data:

code:

COLUMN A	COLUMN B	COLUMN C
20-Mar-14	21-Jun-14	[email]email@domain1.com[/email]
25-Mar-14	13-Jun-14	[email]email@domain2.com[/email]
26-Mar-14	7-Aug-14	[email]email@domain3.com[/email]
20-Mar-14	5-Apr-14	[email]email@domain4.com[/email]
24-Mar-14	12-Jul-14	[email]email@domain5.com[/email]
25-Mar-14	21-Jun-14	[email]email@domain6.com[/email]
26-Mar-14	10-Sep-14	[email]email@domain7.com[/email]
25-Mar-14	22-May-14	[email]email@domain8.com[/email]
24-Mar-14	27-Aug-14	[email]email@domain9.com[/email]
21-Mar-14	21-Jun-14	[email]email@domain10.com[/email]
20-Mar-14	13-Jun-14	[email]email@domain11.com[/email]
25-Mar-14	4-Sep-14	[email]email@domain12.com[/email]
25-Mar-14	2-Jul-14	[email]email@domain13.com[/email]
25-Mar-14	24-Apr-14	[email]email@domain14.com[/email]

So you can see there are multiple rows which contain the same date in column B. I want to take all rows with the same date in column B, and copy them to a new workbook for each group of dates.

So I'd end up with several workbooks called:

13-Jun-14
22-May-14
2-Jul-14
24-Apr-14

And so on...

I have dug around online and this seemed to be the closest thing but it seems to do way too much and when it tries to create the filename based on the date, the slashes in the date format cause errors.

Can anyone point me to a solution?

# ? Mar 27, 2014 18:00

sofokles: Feb 7, 2004; Fuck this

me your dad posted:

I have a workbook with data in columns A, B, and C. Column B contains a series of dates.

There are a few thousand rows containing approximately 10 or so unique dates.

Example data:
code:
COLUMN A	COLUMN B	COLUMN C
20-Mar-14	21-Jun-14	[email]email@domain1.com[/email]
25-Mar-14	13-Jun-14	[email]email@domain2.com[/email]
26-Mar-14	7-Aug-14	[email]email@domain3.com[/email]
20-Mar-14	5-Apr-14	[email]email@domain4.com[/email]
24-Mar-14	12-Jul-14	[email]email@domain5.com[/email]
25-Mar-14	21-Jun-14	[email]email@domain6.com[/email]
26-Mar-14	10-Sep-14	[email]email@domain7.com[/email]
25-Mar-14	22-May-14	[email]email@domain8.com[/email]
24-Mar-14	27-Aug-14	[email]email@domain9.com[/email]
21-Mar-14	21-Jun-14	[email]email@domain10.com[/email]
20-Mar-14	13-Jun-14	[email]email@domain11.com[/email]
25-Mar-14	4-Sep-14	[email]email@domain12.com[/email]
25-Mar-14	2-Jul-14	[email]email@domain13.com[/email]
25-Mar-14	24-Apr-14	[email]email@domain14.com[/email]
So you can see there are multiple rows which contain the same date in column B. I want to take all rows with the same date in column B, and copy them to a new workbook for each group of dates.

So I'd end up with several workbooks called:

13-Jun-14
22-May-14
2-Jul-14
24-Apr-14

And so on...

I have dug around online and this seemed to be the closest thing but it seems to do way too much and when it tries to create the filename based on the date, the slashes in the date format cause errors.

Can anyone point me to a solution?

Edit : Oh, you want the entire row, had to change code then

OOOps aand you want workbooks not sheet. well well. nevermind then. too late.

Edit : Nah, it's never too late.

Couple of simple loops does it

You prolly want to set screenupdating to false

code:


Sub CopyingRowsByDatesToNewBooksForLaterREadingAndSuch()

Dim ws As Worksheet
Dim newBook As Workbook
Dim wb As Workbook
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Dim lastRow As Integer 
Dim hitCount As Integer
Dim dateCollection As New Collection
Dim i As Integer
Dim j As Integer
Dim name As String


' to be sure you really find the last row

With ws
   lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

' find unique dates only

On Error Resume Next
    For i = 2 To lastRow              ' presuming you have columne headers , if not set i = 1 to lastRow
        dateCollection.Add CStr(ws.Cells(i, 2).Value), CStr(ws.Cells(i, 2).Value)
    Next

' loop throught collection find matches and collect row values to new book named DATE

For i = 1 To dateCollection.Count
    name = CStr(dateCollection(i))
    Set newBook = Workbooks.Add
     
    hitCount = 0
         For j = 2 To lastRow '                                       again presuming column header
            If CStr(ws.Cells(j, 2).Value) = dateCollection(i) Then
                hitCount = hitCount + 1
                newBook.Sheets(1).Rows(hitCount).EntireRow.Value = ws.Rows(j).EntireRow.Value
            Else

            End If
         Next
         newBook.SaveAs (name)
         newBook.Close
    Next

End Sub

sofokles fucked around with this message at 00:30 on Mar 28, 2014

# ? Mar 27, 2014 23:10

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

me your dad posted:

Stuff

Haven't tested this, but try...

code:

Sub Test()
	Dim RNG as range	'Cells containing the source data
	Dim CELL as range	'Destination cell
	Dim WB as Workbook	'New Workbook being created in this process
	Dim i as Integer	'Counter
	Dim x as Integer	'Counter
	Dim Arr() as variant	'Source data
	Dim sDate as Date	'Earliest Date in data
	Dim eDate as Date	'Last Date in data
	
	with thisworkbook.sheets(1)
		set rng = .range("A:C").usedrange
		arr() = rng.value
	end with
	
	sdate = application.min(Arr)
	edate = application.max(Arr)
	
	For i = sdate to edate
		if WB is nothing then Set WB = Workbooks.Add
		Set CELL = wb.sheets(1).range("A1")
		
		For x=lbound(Arr,1) to ubound(Arr,1)
			if Arr(x,2) = i then
				cell.value = arr(x,1)
				cell.offset(0,1).value = arr(x,2)
				cell.offset(0,2).value = arr(x,3)
				set cell = cell.offset(1,0)
			end if
		Next x
		
		Set RNG = WB.usedrange
		if RNG is nothing then
		else
			WB.SaveAs Filename:="Report for " & format(i,"DD-MMM-YY") & ".xlsx"
			WB.close
			Set WB = Nothing
		end if	
	Next i
End Sub

# ? Mar 27, 2014 23:35

me your dad: Jul 25, 2006

Cool, thanks y'all. I'm at home now but I'll give those a shot in the morning. I'll let you know how it works out.

# ? Mar 27, 2014 23:37

melon cat: Jan 21, 2010; Nap Ghost

I'm linking some Excel data and putting it into a Word document (just as a regular old table). But for some reason, the linked data keeps getting bolded in Word. There's a space before each number in each cell, and that space seems to be causing this unwanted bolding. If I replace that space with a break space, it seems to fix it. But I don't get why this is happening?

melon cat fucked around with this message at 01:18 on Mar 28, 2014

# ? Mar 28, 2014 01:10

Loving Africa Chaps: Dec 3, 2007; We had not left it yet, but when I would wake in the night, I would lie, listening, homesick for it already.

I've got what im sure is an easy problem i can't sort out by myself.

I have a spreadsheet where i'd like a script to run down part of a coloumn and increment each cell it turn by one untill another cell on the same row becomes 3

code:

Private Sub CommandButton1_Click()
For Each cl In ActiveSheet.Range("B7:B27")
cl.Value = "1"
i = ActiveCell.Offset(0, 10).Select
Do
cl.Value = cl.Value + 1
Loop While i < 3

Next cl

    
End Sub

is what i've got so far but it and variations i've tried either end after the first loop or just go in indefinately incrementing the first cell. I'm sure im doing something retarded, please help

# ? Mar 31, 2014 22:20

esquilax: Jan 3, 2003

Loving Africa Chaps posted:

I've got what im sure is an easy problem i can't sort out by myself.

I have a spreadsheet where i'd like a script to run down part of a coloumn and increment each cell it turn by one untill another cell on the same row becomes 3
code:
Private Sub CommandButton1_Click()
For Each cl In ActiveSheet.Range("B7:B27")
cl.Value = "1"
i = ActiveCell.Offset(0, 10).Select
Do
cl.Value = cl.Value + 1
Loop While i < 3

Next cl

    
End Sub
is what i've got so far but it and variations i've tried either end after the first loop or just go in indefinately incrementing the first cell. I'm sure im doing something retarded, please help

The main issue is that your Do loop doesn't increment i at all.
The code sets i = ActiveCell.Offset(0, 10).Select, then i doesn't ever change while you're in the loop. Try setting i to the value in the target cell somewhere inside the Do loop.

I doubt you'll be able to make the method with ActiveCell.Offset(0, 10).Select work. Try something like i = cl.Offset(0, 10).Value

# ? Mar 31, 2014 22:34

Loving Africa Chaps: Dec 3, 2007; We had not left it yet, but when I would wake in the night, I would lie, listening, homesick for it already.

esquilax posted:

The main issue is that your Do loop doesn't increment i at all.
The code sets i = ActiveCell.Offset(0, 10).Select, then i doesn't ever change while you're in the loop. Try setting i to the value in the target cell somewhere inside the Do loop.

I doubt you'll be able to make the method with ActiveCell.Offset(0, 10).Select work. Try something like i = cl.Offset(0, 10).Value

yeah that was stupid of me.

code:

Private Sub CommandButton1_Click()
For Each cl In ActiveSheet.Range("B7:B27")
    cl.Value = "0"
   
    
    
Do
cl.Value = cl.Value + 1
Loop While cl.Offset(0, 10).Value < 3

Next cl

    
End Sub

works perfectly

# ? Mar 31, 2014 22:50

melon cat: Jan 21, 2010; Nap Ghost

I want to automate a simple process in Excel. I have a spreadsheet with about 3000 names. First then last name. All in one column. To clarify, what I have is about 3000 names formatted like this:

code:

[     A     ][     B     ]
First, Last      
First, Last
First, Last
First, Last

But what I want is this:

code:

[     A     ][     B     ]
First            Last
First            Last
First            Last
First            Last

I want to separate the first name into one column, and the second name into a the second column next to it.

Any fast way to do this, or will I have to go through the gruelling, manual task of copy and pasting the the last name into the next column over? :ohdear:

melon cat fucked around with this message at 18:46 on Apr 1, 2014

# ? Apr 1, 2014 18:33

EAT THE EGGS RICOLA: May 29, 2008

melon cat posted:

I want to automate a simple process in Excel. I have a spreadsheet with about 3000 names. First then last name. All in one column.

I want to separate the first name into one column, and the second name into a the second column next to it.

Any fast way to do this, or will I have to go through the gruelling, manual task of copy and pasting the the last name into the next column over?

data tab, text to columns

# ? Apr 1, 2014 18:44

melon cat: Jan 21, 2010; Nap Ghost

EAT THE EGGS RICOLA posted:

data tab, text to columns

Thanks a lot for telling me about this.

# ? Apr 1, 2014 21:05

FAN OF NICKELBACK: Apr 9, 2002

EAT THE EGGS RICOLA posted:

data tab, text to columns

Holy poo poo. I have been using mid/find/right/left/len/etc for that for over a year.

# ? Apr 2, 2014 03:40

melon cat: Jan 21, 2010; Nap Ghost

EAT THE EGGS RICOLA posted:

data tab, text to columns

Follow-up question on this. This works beautifully for First, Last name scenarios. But if I have a cell that has Firstname, Middle Initial, Lastname this task isn't as streamlined. Example:

Here's what I'm given:

code:

[     A     ][     B     ]
First, X. Last      
First, Y. Last
First, Z. Last

Then I do Text To Columns, and it gives me this:

code:

[     A     ][     B     ][     C     ]
First              X.          Last
First              Y.          Last
First              Z.          Last

How do I merge Columns 'B' and 'C' so it looks like this in the end?

code:

[     A     ][     B     ]
First           X. Last
First           Y. Last
First           Z. Last

# ? Apr 2, 2014 17:14

sofokles: Feb 7, 2004; Fuck this

melon cat posted:

Follow-up question on this. This works beautifully for First, Last name scenarios. But if I have a cell that has Firstname, Middle Initial, Lastname this task isn't as streamlined. Example:

code:

[     A     ][     B     ][     C     ][    D   ]
First              X.          Last	=B1&" "&C1
First              Y.          Last	=CONCATENATE(B2," ",C2)

Will produce

code:

[     A     ][     B     ][     C     ][     D     ]
First          	X.          Last	 X. Last
First          	Y.	    Last	 Y. Last

# ? Apr 2, 2014 17:52

Zaffy: Sep 15, 2003

FAN OF NICKELBACK posted:

Holy poo poo. I have been using mid/find/right/left/len/etc for that for over a year.

Me too, I know nothing.

# ? Apr 3, 2014 14:27

Zorak of Michigan: Jun 10, 2006

Once you do text to columns, it becomes the default for future pastes, which can be a nuisance. You turn it off by doing text to columns, delimited, and then deselecting all delimiters. Or that's how I do it, anyway. I had to do a lot of that this week, since I've been doing reports that required me to paste UNIX df output into Excel.

# ? Apr 3, 2014 22:25

Ragingsheep: Nov 7, 2009

I know that the proper answer is probably along the lines of "use a database" but any tips on optimizing a sumifs formula on 600,000+ rows of data?

# ? Apr 7, 2014 04:02

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Ragingsheep posted:

I know that the proper answer is probably along the lines of "use a database" but any tips on optimizing a sumifs formula on 600,000+ rows of data?

Probably best thing to do would be to add a column concatenating multiple Cells into one. Then use your sumif() on that single column as your criteria.

# ? Apr 7, 2014 14:20

khazar sansculotte: May 14, 2004

Here is my situation:

Employees receive annual evaluations. They can receive evaluations of "awesome," "decent," or "lovely." I would like to generate a table of rows for all possible combinations of evaluations over, say, a four-year period. Something like:

code:

Year 1 | Year 2 | Year 3 | Year 4
 A     | A      | A      | A
 A     | A      | A      | D
 A     | A      | D      | A 
 A     | D      | A      | A
 D     | A      | A      | A
 A     | A      | D      | D
 A     | D      | A      | D
etc. up to row 81 or whatever

I can do this with no problem in R or Matlab or something and just drop the result into Excel, but my colleagues can't, and I'm trying to come up with something they can readily use as well. Can Excel do something like this? I just started learning VBA, if that would be helpful at all.

# ? Apr 7, 2014 16:32

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

You want the solver tool for that one. Haven't used it in like 6 years though so I forgot how to use it, but it'll definitely solve(er) your problem!

Veskit fucked around with this message at 17:37 on Apr 7, 2014

# ? Apr 7, 2014 16:40

khazar sansculotte: May 14, 2004

I couldn't figure out how to make the Solver tool generate rows, however it will help me solve the problem that results from generating the rows, so thanks for pointing it out!

I did find a pretty simple VBA solution to my original problem:

code:

Dim i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer, k As Integer

k = 1

For i1 = 0 To 2
For i2 = 0 To 2
For i3 = 0 To 2
For i4 = 0 To 2
    Cells(k, 1).Value = i1
    Cells(k, 2).Value = i2
    Cells(k, 3).Value = i3
    Cells(k, 4).Value = i4
    k = k + 1
Next i4
Next i3
Next i2
Next i1

I would then do a find/replace to change 0,1,2 to A,D,S.

Follow-up question: if someone else has more or fewer evaluation results (just Awesome/lovely, or Awesome/Decent/lovely/Really lovely) and wants to do it across more or fewer years, are they going to have to futz around with the code themselves, or is there a way to pop up a dialogue box that asks "How many different evaluation results?" and "How many years?" and the user can just put the two numbers in and get what they want?

# ? Apr 7, 2014 19:18

esquilax: Jan 3, 2003

Ronald McReagan posted:

I couldn't figure out how to make the Solver tool generate rows, however it will help me solve the problem that results from generating the rows, so thanks for pointing it out!

I did find a pretty simple VBA solution to my original problem:
code:
Dim i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer, k As Integer

k = 1

For i1 = 0 To 2
For i2 = 0 To 2
For i3 = 0 To 2
For i4 = 0 To 2
    Cells(k, 1).Value = i1
    Cells(k, 2).Value = i2
    Cells(k, 3).Value = i3
    Cells(k, 4).Value = i4
    k = k + 1
Next i4
Next i3
Next i2
Next i1
I would then do a find/replace to change 0,1,2 to A,D,S.

Follow-up question: if someone else has more or fewer evaluation results (just Awesome/lovely, or Awesome/Decent/lovely/Really lovely) and wants to do it across more or fewer years, are they going to have to futz around with the code themselves, or is there a way to pop up a dialogue box that asks "How many different evaluation results?" and "How many years?" and the user can just put the two numbers in and get what they want?

You can do that by using a mathematical algorithm instead of a coding one.

For example, you have n+1 number of grades (A,D,S) and m number of years.

code:

m = 4
n = 2

For i = 0 To (n+1) ^ m - 1
For j = 1 To m

Cells(i + 1, j).Value = Int((i Mod (3 ^ j)) / (3 ^ (j - 1)))

Next j
Next i

esquilax fucked around with this message at 20:32 on Apr 7, 2014

# ? Apr 7, 2014 20:28

khazar sansculotte: May 14, 2004

Brilliant, thanks y'all!

edit: might the 3 in Mod(3^j) as well be n+1 ?

khazar sansculotte fucked around with this message at 22:50 on Apr 7, 2014

# ? Apr 7, 2014 22:47

esquilax: Jan 3, 2003

Oops, yep.

# ? Apr 7, 2014 22:52

cadmaniak: Nov 21, 2006; Le God

Hi there, wondering if anyone could help.

I have a dashboard setup which I have been trying to set up a macro for. The information is laid out over many worksheets in an Excel Workbook.

Ideally I'd like to be able to attach a macro to a button that when run will unhide all the hidden sheets, provide a drop down menu list to select from, go to the selected sheet and then hide all non selected sheets again without the screen updating.

Is this actually possible? The concept sounds simple but while I can implement the switching between sheets macro, it doesn't work when the other sheets are hidden..

Any help would be much appreciated!

# ? Apr 8, 2014 13:52

Tots: Sep 3, 2007

This should be easy, but I can only find solutions if I want to define the criteria manually which I can't.

Say I have a large data set with two columns. Column A has some number and column B has an ID. How can I sum all values in column A that match ID n? The only thing I found through googling involved manually defining criteria.

E.G.

code:

A........B
12.......2
24.......1
02.......5
44.......5
102......2
2........6
12.......1
42.......2
05.......7
44.......8

Sum all numbers in column A that correspond to number 2 in column B etc... My dataset is much larger and more complex than this, but this should be a good example of what I need.

E: Okay, this seems to (sort of) work based on the recommended pivot tables. Although in one of the first test data sets I tried it in there was a number that didn't work out to what it was supposed to. I checked it several times (it was a small test dataset) and it definitely didn't add up for some (sum hah) reason. In all the datasets I tested afterwards it worked fine though.

Tots fucked around with this message at 06:20 on Apr 13, 2014

# ? Apr 13, 2014 05:17

Adbot: ADBOT LOVES YOU

# ? Apr 28, 2024 17:46

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Yes, pivot tables will do what you want. If it didn't come out correctly on one example, it may have to do with some bad labeling on your part, like different spellings (or extra spaces) for the same logical category in column A, or confusion between numbers being recognized as actual numbers or as strings.

In your example, do you expect the sum corresponding to A=2 to be 6, or 11? Depending on how your source data is formatted and how you import it into Excel, "02" may automatically be parsed to the number 2, and you'll get 11. Otherwise you may get 6 for 2, and 5 for "02" separately.

# ? Apr 13, 2014 16:03

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »