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 »

me your dad: Jul 25, 2006

Thanks. I'm a novice at best, and running into a few errors when trying the macro. Please forgive me for any idiocy because I had to make slight edits to some lines to get them to stop producing errors. But I'm getting an error now I can't fix.

I set things up as follows into a module, and I'm getting "Compile error: Method or data member not found" on ws2.cell(i, 1).Value = ws1.cell(rows(i), 1).Value

code:


Public Sub RowNumbers()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rows(50) As Double
Dim hits As Double
Dim i As Integer


Set ws1 = ActiveSheet
hits = 0

For i = 1 To lastrow
   If Active.cell(i, 1).Value <> "" Then
       If Active.cell(i - 1, 1) = "" Then
           hits = hits + 1
           rows(hits) = i
       ElseIf Active.cell(i + 1, 1) = "" Then
           hits = hits + 1
           rows(hits) = i
       End If
    End If
Next

Set ws2 = ActiveWorkbook.Sheets.Add

For i = 1 To hits
   If i - (2 * (i \ 2)) = 1 Then
      ws2.cell(i, 1).Value = ws1.cell(rows(i), 1).Value
      ws2.cell(i, 2).Value = ws1.cell(rows(i), 2).Value
      ws2.cell(i, 3).Value = ws1.cell(rows(i), 3).Value
   Else
      ws.cell(i - 1, 4).Value = ws1.cell(rows(i), 1).Value
   End If
Next

End Sub

# ? Jun 4, 2014 17:42

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 15:05

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

me your dad posted:

I'm getting "Compile error: Method or data member not found" on ws2.cell(i, 1).Value = ws1.cell(rows(i), 1).Value

The error message means that something after a "." doesn't exist. In this case, there is no "cell" property on the Worksheet object. You're looking for "Cells".

# ? Jun 4, 2014 23:24

Talby: Nov 28, 2002

At work, I've started playing around with the macros that they've had for years to try to improve them. Started out just for myself, but it was really popular, and now my whole department is using my version. My only programming experience before was a semester of C++ in my freshman year of college, but I'm learning a lot about VBA in doing this. I've got an idea for something else I want to work on, but I'm not quite sure how to go about it:

I want to start with a blank workbook, with several blank sheets, and I want to write a macro that, when run, will open up another worksheet whose name changes every day with the date, so today it will be, let's say, C:\work\LP-060514.xlsm. I want to open that as read only, since it is shared, or I want to know how to handle the "This worksheet is currently locked by John Smith" popup. Then, I want to navigate to a tab called AUDIT, filter Column C so that it just shows rows whose column C is "1," and then run a macro that's on LP-060514.xlsm that removes superfluous columns, called Sub RemoveToPrint(). Finally, I want to take everything that's on that sheet and copy it to a specific worksheet on my initial blank workbook.

So, as bullet points, I am hoping to write a macro to:
-Open a shared workbook as read-only
-Navigate to a specific sheet on that book
-Filter a column of that sheet
-Run a macro that is on that workbook
-Copy the result into my original worksheet

I have done variations on a lot of this before, but the things that I am not sure how to do are how to make it open read only, or how to handle it if someone else is in the workbook, I don't know how to filter a column, and I don't know how to run a macro that is in another workbook than the one I started in.

I hope this makes sense, can anyone direct me where to start?

# ? Jun 5, 2014 12:04

Smithersnz: May 10, 2005; We freaked out yesterday. Let's just freak in tonight; Soiled Meat

Zaffy posted:

I get a report every week that looks like this:

code:

STUDENT NAME	Student ID	COURSE NAME		SCORE	PERCENT COMPLETE
Sarah		1		Language Arts 10A 	84.60%	100.00%
Sarah		1		Algebra 2A 		0.00%	0.00%
Sarah		1		Algebra 2B 		30.77%	56.67%
Sophia		2		Physical Education B	84.62%	100.00%
Mark		3		Physical Education A	79.27%	100.00%
Mark		3		Physical Education B	89.78%	100.00%
Justin		4		American History A 	88.04%	100.00%
Justin		4		Language Arts 12A 	67.56%	100.00%
Justin		4		Algebra 2A 		63.49%	100.00%
Justin		4		Geometry A 		60.46%	100.00%
Justin		4		Algebra 2B 		50.36%	96.67%
Carmen		5		Algebra 2A 		65.10%	100.00%
Carmen		5		World History A 	72.80%	100.00%
Herbert		6		Language Arts 12A 	28.73%	89.47%
Herbert		6		Language Arts 12B 	82.46%	100.00%
Herbert		6		Language Arts 11A 	55.30%	100.00%

I've removed about 10 columns for easy reading.

Is it possible to have excel move the data for each student into a single row, rather than having them in a row per class?

Here's how I did this:

Use this first -
=IF(B2=B1,CONCATENATE(G1,",",CONCATENATE(C2,",",E2,",",F2)),CONCATENATE(C2,",",E2,",",F2))

That gives you a result of this:

Then I'd use another formula to get the last (i.e. the full line) of that - =B2=B3

Copy/Paste Special to remove formulas, sort by the true/false line and delete everything that's not a FALSE. Those ones are the last lines, the ones you want to keep. Then just do a text to columns on the new cell, delimited by comma.

# ? Jun 5, 2014 12:57

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Talby posted:

I have done variations on a lot of this before, but the things that I am not sure how to do are how to make it open read only, or how to handle it if someone else is in the workbook, I don't know how to filter a column, and I don't know how to run a macro that is in another workbook than the one I started in.

I hope this makes sense, can anyone direct me where to start?

- Workbooks.Open has an optional parameter called "ReadOnly". Set it to true.

- The Range object has some properties for filtering (AutoFilter? I never use it). Protip: if you can't find something in the documentation but you know how to do it by hand, use the macro recorder, record yourself doing it (e.g. filter a column), then check the produced code to figure out the name of the property/method that is called.

- Application.Run will let you run a macro from a string representing its name (I think you'll have to qualify it with the macro file's name also, so "'C:\work\LP-060514.xlsm'!RemoveToPrint" or something like that...sometimes it doesn't work without single quotes, even if the filepath has no spaces). If the file that contains the macro is not already open, Application.Run will open it. It will not close it afterwards. Also, it will lock the file if it can. So you should open it as read-only yourself, then Application.Run it, then close it yourself.

Question: Is there really a different "RemoveToPrint" every day, one in each daily version of "LP-060514.xlsm"? Otherwise, wouldn't it be easier to move this routine to your main workbook and just pass it a reference to the workbook you want it to act on? You probably want to avoid copying the code over and over unless you really need to (e.g. you want to make sure that LP-060514.xlsm will always run with the version of the code as it was on that date, not whatever the current version is).

# ? Jun 6, 2014 00:10

Zaffy: Sep 15, 2003

Smithersnz posted:

Here's how I did this:

Use this first -
=IF(B2=B1,CONCATENATE(G1,",",CONCATENATE(C2,",",E2,",",F2)),CONCATENATE(C2,",",E2,",",F2))

That gives you a result of this:

Then I'd use another formula to get the last (i.e. the full line) of that - =B2=B3

Copy/Paste Special to remove formulas, sort by the true/false line and delete everything that's not a FALSE. Those ones are the last lines, the ones you want to keep. Then just do a text to columns on the new cell, delimited by comma.

Thanks, that's a huge improvement over how I've been dealing with it so far

# ? Jun 6, 2014 00:45

Talby: Nov 28, 2002

ShimaTetsuo posted:

- Workbooks.Open has an optional parameter called "ReadOnly". Set it to true.

- The Range object has some properties for filtering (AutoFilter? I never use it). Protip: if you can't find something in the documentation but you know how to do it by hand, use the macro recorder, record yourself doing it (e.g. filter a column), then check the produced code to figure out the name of the property/method that is called.

- Application.Run will let you run a macro from a string representing its name (I think you'll have to qualify it with the macro file's name also, so "'C:\work\LP-060514.xlsm'!RemoveToPrint" or something like that...sometimes it doesn't work without single quotes, even if the filepath has no spaces). If the file that contains the macro is not already open, Application.Run will open it. It will not close it afterwards. Also, it will lock the file if it can. So you should open it as read-only yourself, then Application.Run it, then close it yourself.

Question: Is there really a different "RemoveToPrint" every day, one in each daily version of "LP-060514.xlsm"? Otherwise, wouldn't it be easier to move this routine to your main workbook and just pass it a reference to the workbook you want it to act on? You probably want to avoid copying the code over and over unless you really need to (e.g. you want to make sure that LP-060514.xlsm will always run with the version of the code as it was on that date, not whatever the current version is).

I managed to test opening it with Workbooks.Open while not a lot of people were in the office, so I can easily add the ReadOnly paramater tomorrow. Thank you! And actually I used the Macro recorder to get the code for that filter.

The RemoveToPrint function is not different each day, the code from 060514.xlsm will work on 060614 will work on 103015 will work on everything, and I hadn't even thought of just copying it to my main one. It's not a huge chunk of code, and it would be easier to make it do what I want it to do without affecting the macro that everyone else uses.

Basically, LP-date.xlsm is generated every morning and contains more or less my entire job. Each day when I get in, I have to go to about 8 different tabs, filter out my area on each one and print each one, so that I can reference it without keeping the shared file in use. What I want to do instead is just automatically have all of my area filtered out and copied to a workbook of my own.

From there, I have more big ideas, but getting this done will be the first big step. Thank you for your help!

# ? Jun 6, 2014 02:14

Tots: Sep 3, 2007

I thought this problem would be easy to solve, but it's not. Help?

I have two sets of data. The third table is what I want.

code:

Data 1

|Unique ID|Street Address|
|        2|323 Test St   |
|        3|101 Test Rd   |
|        4|404 Blank Ave |
|        5|666 Fire Way  |
|        6|530 Test Pl   |
|        7|920 Computer  |
|        8|240 Ipsum Cr  |
__________________________

Data 2

|Unique ID|Street Address|
|         |323 Test St   |
|         |101 Test Rd   |
|         |404 Blank Ave |
|         |666 Fire Way  |
|         |920 Computer  |
__________________________

Desired Result

|Unique ID|Street Address|
|        2|323 Test St   |
|        3|101 Test Rd   |
|        4|404 Blank Ave |
|        5|666 Fire Way  |
|        7|920 Computer  |
__________________________

Any ideas? Excel or I can import them into access and use SQL (haven't touched SQL in ages.)

# ? Jun 9, 2014 20:31

Ragingsheep: Nov 7, 2009

Index match?

# ? Jun 9, 2014 22:15

WhatsInaMojito: Dec 20, 2011

Index match works:
=INDEX($A$2:$A$8,MATCH(B10,$B$2:$B$8,0))

Vlookup:
Alternatively, you can swap the two columns and use VLookup(). Swapping the columns can be done off to the side.

Match:
=MATCH(B10,$B$1:$B$8,0)
Since your first column is sequential you could just use a match statement.

# ? Jun 10, 2014 03:11

Tots: Sep 3, 2007

Ended up using VLOOKUP since it was the first suggestion and it worked marvelously. Thanks!

# ? Jun 10, 2014 16:41

schmagekie: Dec 2, 2003

I'm trying to get a list of unique values from a selection of multiple columns/ranges on a new sheet using a macro. I have it kind of working, but I'm relatively new VBA and hitting my limit.
If anyone could take a look, I'd really appreciate it.

What's broken:
Needs error handling if a pivot table/chart, etc. is selected.
If multiple selections are in the same column, they're going to different output columns, while I'd like them to be in the same output column.
The output destination should be a range with reasonable dimensions, not the entire column.
I have the output columns increment by one, but I'd like to use the next blank column.
Only the values should be copied, not the formatting.
I can't get sort ascending to work.

code:

Sub Get_Uniques()
''''''''''''''''''''''''
'Variables             '
''''''''''''''''''''''''
Dim rRng As Range           'selection
Dim nRng As Range           'column of selection if more than one
Dim jAreas As Long          'area of selection if non-continuous
Dim sShtNameOrig As String  'source sheet name
Dim sShtName As String      'new sheet name
Dim ws As Worksheet         'new worksheet
Dim bShtCheck As Boolean    'check if new sheet name exists
Dim rDest As Range          'destination for uniques/sorting
Dim i As Long               'increment destination column
Dim j As Long               'count for columns in area

''''''''''''''''''''''''
'Declarations          '
''''''''''''''''''''''''

Set rRng = Selection
sShtName = ActiveSheet.Name
sShtNameOrig = ActiveSheet.Name
i = 1

''''''''''''''''''''''''
'Action                '
''''''''''''''''''''''''

'todo: exit if pivot table/chart, etc is selected

'set new sheet name
If Len(sShtName) <= 24 Then
    sShtName = sShtName & " Unique"
Else:
    sShtName = Left(sShtName, 24) & " Unique"
End If

'check if new sheet name exists, if yes set ws
For Each jWS In Worksheets
    If sShtName = jWS.Name Then
        Set ws = Sheets(sShtName)
        bShtCheck = True
        Exit For
    End If
Next jWS

'if sheet name doesn't exist, add it
If bShtCheck = False Then
    Set ws = Sheets.Add(After:=Sheets(sShtNameOrig), Type:="Worksheet")
    ws.Name = sShtName
End If

'down to business
For jAreas = 1 To rRng.Areas.Count                      'for each area in selection if non-continuous (todo: make multiple selection in the same source column end in the same destination column)
    For j = 1 To rRng.Areas(jAreas).Columns.Count       'for each column in area
        Set nRng = rRng.Areas(jAreas).Columns(j)
    
        Set rDest = ws.Columns(i)                       'todo: make this the next empty column, set an actual range, not entire column
        
        'paste from selection
        nRng.SpecialCells(xlCellTypeConstants).Copy Destination:=rDest      'todo: paste values only, not formats
        
        'remove duplicates
        rDest.RemoveDuplicates Columns:=1, Header:=xlGuess 'xlyes xlno
        
        'sort ascending with headers (not working)
        rDest.SortSpecial xlPinYin, , xlAscending, , , , , , xlYes, , , xlSortColumns
        
        i = i + 1                                       'increment column
    Next j
Next jAreas
    
End Sub

# ? Jun 14, 2014 00:53

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

Do you mind taking a quick screenshot of what you are starting with and how you want the results displayed?

# ? Jun 15, 2014 07:19

schmagekie: Dec 2, 2003

Thanks for checking this out, Old James. Color added for emphasis.
Edit: and if I were to run it again on column C, on the output sheet, F and M should be in column D.

Here's my favorite macro to create a pivot table (with a custom pivot table style) from a selection, or if no selection is made, it uses the current region, and sets the layout to classic.

code:

Sub CreatePivotTable()
'activated by Ctrl + t as defined in Workbook_Open()

Dim rData As Range
Dim pcNew As PivotCache
Dim rCell As Range
Dim iFieldCnt As Long
Dim ptNew As PivotTable
Dim sShName As String
Dim sShNameOrig As String
Dim pf As PivotField
Dim i As Long
Dim ws As Worksheet

Const sField = "Field"
iFieldCnt = 1

ActiveSheet.UsedRange

'make sure a range is selected
If TypeName(Selection) = "Range" Then
    'if a single row is selected, set range to Current Region
    If Selection.Rows.Count = 1 Then
        Set rData = ActiveCell.CurrentRegion
    Else
        Set rData = Intersect(Selection, ActiveSheet.UsedRange)
    End If
    
    'put column headers in blank cells
    For Each rCell In rData.Rows(1).Cells
        If IsEmpty(rCell.Value) Then
            rCell.Value = sField & iFieldCnt
            iFieldCnt = iFieldCnt + 1
        End If
    Next rCell
    
    'set new sheet name to source name & Pivot
    sShNameOrig = ActiveSheet.Name
    sShName = ActiveSheet.Name
    
    If Len(sShName) > 25 Then
        sShName = Left(sShName, 25) & " Pivot"
    Else: sShName = sShName & " Pivot"
    End If

    'add new sheet to the right of source
    Set ws = Sheets.Add(After:=Sheets(sShNameOrig), Type:="Worksheet")
    ws.Name = sShName
    
    'create and set default Pivot Table Style to custom style
    'Call PivotTableStyle
    
    Set pcNew = ActiveWorkbook.PivotCaches.Create(xlDatabase, rData)
    Set ptNew = pcNew.CreatePivotTable(ws.Range("A3"))
    
    'set layout to Classic
    With ptNew
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
        .ColumnGrand = True
        .RowGrand = False
        '.RepeatAllLabels xlRepeatLabels
        .ShowTableStyleRowHeaders = False
        .ShowDrillIndicators = False
        .HasAutoFormat = True
        .SaveData = True
        .ManualUpdate = True
    End With
    
    ws.Range("A3").Select
    
End If

End Sub

Only registered members can see post attachments!

schmagekie fucked around with this message at 09:21 on Jun 15, 2014

# ? Jun 15, 2014 08:37

Ragingsheep: Nov 7, 2009

Is it just me or does Excel 2013 just seem really buggy? I've had problems with it randomly "freezing", UI elements will disappear, opening new files will effectively open a new Excel windows that's 100% transparent, etc?

# ? Jun 16, 2014 01:22

Joose Caboose: Apr 17, 2013

Does anyone use the OneStream XF Excel AddIn? I use it for work and am trying to use VBA to code a macro in Excel 2010. I can't figure out how to get the macro to go into the OneStream XF tab and select Refresh XF Data. When I try to record one of clicking it, it just gives me Application.CalculateFull but this alone just recalculates and doesn't refresh the retrieve from OneStream?

Anyone have any experience with this?

# ? Jun 16, 2014 15:45

kiwid: Sep 30, 2013

I've been tasked to update some monstrosity of an Excel document filled with macros. I open it up and there is zero code in the VBA editor. Nothing.

If I open up the macro screen and click to edit one of the macros, it redirects me to a worksheet like this:

What the gently caress is this? How is a macro running from code inside a cell? This Excel document is old as gently caress, like back in 1998ish.

# ? Jun 18, 2014 19:53

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

kiwid posted:

I've been tasked to update some monstrosity of an Excel document filled with macros. I open it up and there is zero code in the VBA editor. Nothing.

If I open up the macro screen and click to edit one of the macros, it redirects me to a worksheet like this:

What the gently caress is this? How is a macro running from code inside a cell? This Excel document is old as gently caress, like back in 1998ish.

I'd never heard of this, looks like it's from Excel 4.0. Wish I could help, but your best bet is probably to try to take each of those individual cells and convert them into whatever their modern equivalent is, probably Functions within Modules.

# ? Jun 18, 2014 22:24

kiwid: Sep 30, 2013

DukAmok posted:

I'd never heard of this, looks like it's from Excel 4.0. Wish I could help, but your best bet is probably to try to take each of those individual cells and convert them into whatever their modern equivalent is, probably Functions within Modules.

Oh nice find, I wasn't able to Google search what it was which was why I asked here.

Though, the boss doesn't want me to update it for VBA, so gently caress my life.

# ? Jun 18, 2014 23:59

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Yeah, the Excel 4 style macros are the ancestor to having VBA in Office. You can execute a legacy Excel 4 macro from within VBA by using Application.ExecuteExcel4Macro, if that helps.

I wouldn't worry about them disappearing from later versions of Excel, specifically because dumb old people who have been running the same spreadsheet for 20 years like your boss are Office's main customers. But it would probably just be easier to convert it to VBA, unless it's much more complicated than what you've shown here.

Related: Last year I received a spreadsheet from a data provider that seemed to be cut short at 16384 rows. The latest version of Excel to have such a limitation was released in 1995.

# ? Jun 19, 2014 00:45

Hughmoris: Apr 21, 2007; Let's go to the abyss!

Is it possible to write a script that will go through a spreadsheet, and if a row has a value, hide that row? I've started a new job and am trying to teach myself python scripting at the same time. Today I was tasked with reading through a spreadsheet, and if a cell had a certain string, I wanted to hide that entire row. Tedious stuff, and I'm guessing there is a way to automate it.

I think i can fuddle my way through writing a basic script but I'm unsure how to issue a "hide row" command.

# ? Jun 27, 2014 23:47

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

Hughmoris posted:

Is it possible to write a script that will go through a spreadsheet, and if a row has a value, hide that row? I've started a new job and am trying to teach myself python scripting at the same time. Today I was tasked with reading through a spreadsheet, and if a cell had a certain string, I wanted to hide that entire row. Tedious stuff, and I'm guessing there is a way to automate it.

I think i can fuddle my way through writing a basic script but I'm unsure how to issue a "hide row" command.

code:

Sub Hide_Row()
   Dim CELL As Range

   For Each CELL In Selection
      If Len(CELL.Value) > 0 Then CELL.EntireRow.Hidden = True
   Next CELL
End Sub

# ? Jun 28, 2014 00:37

Hughmoris: Apr 21, 2007; Let's go to the abyss!

Old James posted:

code:

Sub Hide_Row()
   Dim CELL As Range

   For Each CELL In Selection
      If Len(CELL.Value) > 0 Then CELL.EntireRow.Hidden = True
   Next CELL
End Sub

Thanks for this. A couple of dumb questions. What language is this, VB? How would I adjust it if the keyword I'm looking for is either "nausea" or "vomiting". The cell may have both.

# ? Jun 28, 2014 01:20

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Hughmoris posted:

Thanks for this. A couple of dumb questions. What language is this, VB? How would I adjust it if the keyword I'm looking for is either "nausea" or "vomiting". The cell may have both.

It's VBA, the standard scripting language for Excel, as is probably everything in this thread. It is probably the easiest way to script Excel (although if you just need to read Excel documents it is not much more difficult in other languages). If you feel like using Python you may have better luck in the Python thread because AFAIK there are several different Python/Excel libraries and I doubt many people in this thread are very familiar with (all of) them.

However: if the Excel library you are using in Python is built in a way that reflects the Excel object model, you should look for objects in something that should look like the collection hierarchy Application->Workbooks->Worksheets->Cells. Cells is a collection of Range objects, each of which should have a Value property which holds its contents, and a Hidden property that can make it invisible when set to true (although it only works if a Range spans an entire row or column). Build a list of Range objects however you need then iterate over them, making each corresponding row hidden.

Also, unless there is more to your problem, can't you just use a filter in the spreadsheet, without any coding?

# ? Jun 28, 2014 03:39

Elucidarius: Oct 14, 2006

Hey guys, I'm having trouble understanding VLOOKUPS and was wondering if I could get some help.

My tables has the following information (in this order).

Employee Employee ID Manager

I need to figure out how to VLOOKUP all the people under a certain manager. I've spent the last hour and a half racking my brain trying to figure this out but I've come up on a bunch of errors and no actual results.

Thanks.

# ? Jun 29, 2014 00:20

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

VLookup only returns values to the right of the column you are matching on and that is your problem.

To get around this people commonly use a combination of the INDEX() and MATCH() functions. I throw an IFERROR() around it all so I don't have messy error codes when there is no match.

=IFERROR(INDEX([Employee],MATCH("John Smith",[Manager],0)),"")

# ? Jun 29, 2014 02:38

WhatsInaMojito: Dec 20, 2011

Elucidarius posted:

Hey guys, I'm having trouble understanding VLOOKUPS and was wondering if I could get some help.

My tables has the following information (in this order).

Employee Employee ID Manager

I need to figure out how to VLOOKUP all the people under a certain manager. I've spent the last hour and a half racking my brain trying to figure this out but I've come up on a bunch of errors and no actual results.

Thanks.

Vlookup is useful when one unique index refers to one item in the table. Don't forget when using Vlookup to include the FALSE statement as the fourth praram when your data isn't already sorted. In your case, an auto filter will quickly give you all the employee's under a specific manager. Likewise sorting might help group the Employee's by manager.

Another way might be to use Pivot tables, but they are likely more challenging than needed for this problem

Edit: Old James is correct about the index/match.

WhatsInaMojito fucked around with this message at 02:48 on Jun 29, 2014

# ? Jun 29, 2014 02:42

Tortilla Maker: Dec 13, 2005; Un Desmadre A Toda Madre

Say I have values in Column A that consist of a letter+number. For example, cell A1 has the value of "D25".

Each row in Column D contains it's own value, for example cell D25 contains the value "Apple".

In Column B, I need to use the info in the corresponding cell in Column A to pull the corresponding value from Column D. For example, I would want cell B1 to read "Apple" since A1 points to D25 which has the value "Apple".

Is there a formula to do this considering I have thousands of rows I need to apply this to?

Thanks!

Edit: Figured it out via the "indirect" function. Thanks though!

Tortilla Maker fucked around with this message at 18:35 on Jun 30, 2014

# ? Jun 30, 2014 17:50

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Looking for a way to pull some monthly numbers for a team of folks I manage and I'm running into a problem with the way our business system handles account adjustments with taxes. For example, I'm attempting to determine the total number of adjustments (meaning any time we manually issued a credit or debit to an account) each of my folks has completed in the month of June. When I credit/debit any charge that has a tax associated with it, the following occurs:

Customer Service Fee/Courtesy Credit: -$10.00
Tax on: Customer Service Fee: -$0.70

The second entry is not a 2nd adjustment manually done, it is an automatic calculation, but because it is itemized separately, when I just run a COUNTIF to sum up the numbers for each user, anything that has a tax gets counted twice (and we do some business in Texas, and certain charges there actually have 3 different taxes, so 1 adjustment shows up as 4 different ones). I want to remove these 'Tax On:' line items from my calculations in terms of the # of adjustments completed, however I don't want to actually remove the line items entirely because I'd also like to run something that sums up total credits issued, which should include the tax dollars credited or assessed. The columns in my spreadsheet look like this:

code:

Account # | Account Type | Line Item 				| Invoice # | $ Amount | User   | Timestamp
123456789 | Residential	   Customer Service Fee/Credit		  1401		-10.00	 Kumba	  6/1/14 10:39
123456789 | Residential	   Tax on: Customer Service Fee/Credit	  1401		-0.70	 Kumba	  6/1/14 10:39

What I was doing is just =COUNTIF(F1:F2200,"Kumba") to get the numbers, but obviously something that simple isn't going to work for me in this case. Is there possibly something I could macro via VBA to make a copy of this sheet with anything that includes the string 'Tax on:' deleted?

Also looking for advice as to how to get the total credit dollars each user gave - what I would normally do is just use a filter to filter by user, remove any positive numbers (debits) via the filter, and sum what remains, but I'd like something more automated so I don't have to do this manually every month. I tried reasoning my way through this trying to use VLOOKUP somehow but I'm not sure that's the right direction.

# ? Jul 2, 2014 23:01

ZerodotJander: Dec 29, 2004; Chinaman, explain!

I hope you meant SUMIF and not COUNTIF in your question.

Also you can just do this with a wildcard condition.

=SUMIFS(E:E, F:F, "Kumba", C:C, "<>Tax on*") will give you total for Kumba not including rows marked "Tax on".

# ? Jul 2, 2014 23:11

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

ZerodotJander posted:

I hope you meant SUMIF and not COUNTIF in your question.

Also you can just do this with a wildcard condition.

=SUMIFS(E:E, F:F, "Kumba", C:C, "<>Tax on*") will give you total for Kumba not including rows marked "Tax on".

I meant COUNTIF, cause in that example I was just counting the number of individual adjustments completed, not how much the actual value of the adjustment is (for comparative purposes, so if Person A did 10 adjustments and Person B did 735, I know there's a lazy person problem). I did not know this =SUMIFS function existed and I think it will do exactly what I needed. Thank you a ton!

# ? Jul 2, 2014 23:37

ScarletBrother: Nov 2, 2004

I have a sheet with a 4 option pulldown in one cell. I want a range of cells to display one of four numbers based upon what is selected in the pulldown cell. Please help!

# ? Jul 8, 2014 20:10

EAT THE EGGS RICOLA: May 29, 2008

ScarletBrother posted:

I have a sheet with a 4 option pulldown in one cell. I want a range of cells to display one of four numbers based upon what is selected in the pulldown cell. Please help!

Make another sheet and store a table mapping the key (your pulldown value) to a value, then use vlookup in the cells that you want to display the values based on the keys.

# ? Jul 8, 2014 20:53

ScarletBrother: Nov 2, 2004

I got it to work using IF(Cell='foo','blah',IF(...

# ? Jul 8, 2014 20:56

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

If you want to get clever with it try this (assuming your drop down is in cell A1 and has values A, B, C, & D).

=choose(match(A1,{"A","B","C","D"},0),"foo","bar","rab","oof")

# ? Jul 8, 2014 21:29

khazar sansculotte: May 14, 2004

I have a bunch of pdfs that I'll be converting to .xlsx. The excel files are going to look like garbage of course (columns all crazy, etc... if you've ever used Acrobat's .pdf to .xls converter, you know what I'm talking about), so I'm trying to extract the useful data from them to insert into a nice new spreadsheet that can actually be made sense of. I have a template that's going to be used to dump each sheet into.

The data are financial, 1 year of data for one department within the organization is contained on two different sheets (one for original budget at the beginning of the fiscal year, one for actual budget at the end of the fiscal year). There are about 70 departments and five years of data for each of them, so a total of about 700 sheets.

My converted pdf files look something like this (the whitespace below may denote entirely blank columns, or whitespace within a cell):

code:

Account #     Dollars
1000           $2900
110                          $800
111       $400
112              $300
120                     $700
130                                                      $200
135                 $500

Obviously I want them to look more like this:

code:

Account #     Dollars
110           $800
111           $400
112           $300
120           $700
130           $200
135           $500
1000          $2900

(the Account # 1000 is a total, they're on the top in the original PDF but I want them on the bottom in the spreadsheet).

By itself I don't think this would be too challenging. However, there are two things here that make it more complicated:

1) Not every PDF I'm converting has entries for all of the same accounts. Due to stupid reporting practices, if an account reports $0 in revenue or expenses, it doesn't actually show $0, it just doesn't show the account at all. The template will actually list all of the accounts, so I need to come up with code that does some kind of "scan and match" thing, and defaults to $0 if it can't find the account in the garbled sheet.

2) Some of the accounts have subaccounts that are listed along a second axis, which is annoying by itself, but this also causes them to spill over onto extra pages. For example:

code:

               Subaccount
Account #      Total       10          20          30          40                        
200            $700        $100        $100        $100        $100 
300            $700        $100        $100        $100        $100
400            $700        $100        $100        $100        $100
500            $700        $100        $100        $100        $100

               Subaccount
Account #      50          60          70
200            $100        $100        $100
300            $100        $100        $100
400            $100        $100        $100
500            $100        $100        $100

(Obviously not all of the dollars are the same, I just don't have the patience to make up different ones each time). I want to paste these into my new sheet like so:

code:

Account #   Dollars
200         $700
10          $100
20          $100
30          $100
40          $100
50          $100
60          $100
70          $100

300         $400
10          $100
20          $100
30          $100
40          $100
50          $100
60          $100
70          $100

etc.

I feel like I've done a decent job picking up VBA here and there on the fly to handle other smaller projects, so I don't think this is totally beyond my reach, but I'm a little overwhelmed by the (seemingly) immense amount of effort it would take to do all of this. Is there any hope?

# ? Jul 9, 2014 14:36

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

Ronald McReagan posted:

Is there any hope?

Assuming you're somewhat handy with VBA you can loop through the cells and try something like.

If Empty delete cell, move cells left.
If not empty trim contents.

Trim(String) will work for that. Maybe Trim(Cstr('Cell Contents')) can't remember if it demands a string or not.

It should end up formatting things somewhat better for you assuming it doesn't start mashing up multiple values into one cell. If it does that you've got a whole different problem.

[Edit - Did you edit your post or am I terrible at reading? This probably doesn't solve your problem at all sorry I'll take another look when I have a spare few minutes]

Cast_No_Shadow fucked around with this message at 11:07 on Jul 10, 2014

# ? Jul 10, 2014 11:04

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

Ok taking another look you should break this down into the problems you want to solve.

Step one, clean up your whitespace.

Step two, get rid of unwanted cells

Step three, move the sub columns into the new

Parent
Child
Child
Child
Parent

Type structure.

My confusion is about the $0 entries and overall structure of the sheets.

If with the sub accounts you have $0 entries how do you know in the garbled mess? You can pull them all across so they line up with the method in my previous post but otherwise there is no way to know the difference between a blank cell because its garbled and a blank cell representing $0.

All I can suggest would be to pull everything left and then manually check. If we're talking huge amounts here have it run through again and any Child account number without a figure under it highlight a color to help check.

Once you get to there though, it should be nicely formatted and moving data from the horizontal axis to the vertical should be pretty simple.

# ? Jul 10, 2014 11:20

schmagekie: Dec 2, 2003

Here are a couple subs to shift everything you select to the left, and delete the entire row if column B = 0.

code:

Sub AlignLeft()
    Dim oRng As Range
    Dim jCol As Long
      
On Error GoTo ErrorHandler

    Set oRng = Intersect(Selection, ActiveSheet.UsedRange)

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    On Error Resume Next
    
    For jCol = 1 To oRng.Columns.Count
        oRng.Columns(1).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
    Next jCol
    
    On Error GoTo 0
        
ErrorExit:
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
Exit Sub
    
ErrorHandler:
    MsgBox Err.Description, vbCritical
Resume ErrorExit

End Sub

code:

Sub DeleteBlanksInColB()
    Dim oRng As Range
    Dim jRows As Long
    Dim nCounter As Long
    
On Error GoTo ErrorHandler

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    Set oRng = Intersect(ActiveSheet.Range("B:B"), ActiveSheet.UsedRange)
    
    jRows = oRng.Rows.Count

    For nCounter = jRows To 1 Step -1
        If Cells(nCounter, 2).Value2 = 0 Then
            Cells(nCounter, 2).EntireRow.Delete
        End If
    Next nCounter
    
ErrorExit:
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
Exit Sub
    
ErrorHandler:
    MsgBox Err.Description, vbCritical
Resume ErrorExit

End Sub

schmagekie fucked around with this message at 06:48 on Jul 11, 2014

# ? Jul 11, 2014 06:26

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 15:05

FAN OF NICKELBACK: Apr 9, 2002

Ronald McReagan posted:

My converted pdf files look something like this (the whitespace below may denote entirely blank columns, or whitespace within a cell):
code:
Obviously I want them to look more like this:
code:
2) Some of the accounts have subaccounts that are listed along a second axis, which is annoying by itself, but this also causes them to spill over onto extra pages. For example:
code:
(Obviously not all of the dollars are the same, I just don't have the patience to make up different ones each time). I want to paste these into my new sheet like so:
code:
I feel like I've done a decent job picking up VBA here and there on the fly to handle other smaller projects, so I don't think this is totally beyond my reach, but I'm a little overwhelmed by the (seemingly) immense amount of effort it would take to do all of this. Is there any hope?

I copied your post code for the raw data you're working with, pasted into word and then again to Excel, used Data tab > Text to Columns (spaces as the delimiter). I don't work with PDFs so I'm guess it's similar for you?

Anyway

The getting stuff in line part is pretty easy I think. I just used 2D arrays, since you can basically do whatever through however much you want and it's almost instant (you're basically asking excel to memorize the sheet and do everything in it's head as opposed to forcing it to write down each step as it goes into the cells).

code:

Sub GetInLine()
Dim i, j, x, k As Integer
Dim Mess As Variant
Dim Fixed As Variant
Dim AllThisStuff As Range
Dim Ws As Worksheet

For Each Ws In Worksheets
Ws.Activate
Set AllThisStuff = ActiveSheet.UsedRange
Mess = AllThisStuff
ReDim Fixed(1 To UBound(Mess), 1 To UBound(Mess, 2))
For i = 1 To UBound(Mess)
    Count = 0
    For j = 1 To UBound(Mess, 2)
        If Mess(i, 1) = "" Then
            Exit For
        Else
            If Mess(i, j) <> "" Then
                Fixed(i, j - Count) = Mess(i, j)
            Else
                Count = Count + 1
            End If
        End If
    Next j
Next i
AllThisStuff = Fixed
Next Ws
End Sub

I briefly tested it with more and less rows and didn't have any issue at all.

For the one to fix the split rows you need to make sure to adjust Set ReDim Preserve Mess(1 To UBound(Mess), 1 To 12) to whatever your actual range will be after you paste your stuff. If you don't tell an array to make room for things that don't exist yet, it won't be able to write to anything outside of the original "usedrange."

code:

Sub UnSplitAccounts()
Dim i, j, x, k As Integer
Dim Mess As Variant
Dim Fixed As Variant
Dim AllThisStuff As Range
Dim Ws As Worksheet


For Each Ws In Worksheets
Ws.Activate
Set AllThisStuff = ActiveSheet.UsedRange
Mess = AllThisStuff
ReDim Preserve Mess(1 To UBound(Mess), 1 To 12) 
'change 12 to however many columns the worksheet will be using in it's final state

For i = 1 To UBound(Mess)
    For x = -1 To -100 Step -1
        If i + x <= 1 Then
        Exit For
        Else
            If Mess(i, 1) = Mess(i + x, 1) And Mess(i, 1) <> "" Then
                For j = 1 To UBound(Mess, 2)
                    If Mess(i + x, j) = "" Then
'I was lazy and this next part could/should be a loop, but you get the idea
'and it should work regardless, provided your example has the same 4 rows as your actual data.
                        Mess(i + x, j) = Mess(i, 2) 
                        Mess(i + x, j + 1) = Mess(i, 3)
                        Mess(i + x, j + 2) = Mess(i, 4)
                        Mess(i, 1) = ""
                        Mess(i, 2) = ""
                        Mess(i, 3) = ""
                        Mess(i, 4) = ""
                        Exit For
                    End If
                Next j
            End If
        End If
    Next x
Next i
AllThisStuff = Mess
Next Ws
End Sub

Make sense? Essentially you define a range, then throw the whole thing into an array. After that you can just mess with it in much the same was as you would with something like "Sheets("Butts").Cells(1,2)" since it saves everything to the same positions they were at in the range. Then you make the range equal the value of the array in one fell swoop.

Trust me, it's fast as heck and I use it when dealing with ~200,000+ cells at a time--it's almost instant for row/column removal etc. (which you do similar to the second hunk of code I posted, by writing from one array to another and skipping/keeping track of skipped blanks).

I didn't get to your $0 thing because I wasn't sure exactly what you meant (sorry, it's late and I didn't have time to skate through your post again, but I will try tomorrow evening if I get the chance).

# ? Jul 11, 2014 07:27

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

«‹›82 »