DPChallenge: A Digital Photography Contest You are not logged in. (log in or register
 

DPChallenge Forums >> General Discussion >> HELP!! Excel +VBA +Macro experts
Pages:  
Showing posts 1 - 21 of 21, (reverse)
AuthorThread
07/19/2011 08:34:34 PM · #1
I'm almost through with the percentile ranking of all users from the beginning of DPC to the last challenge. BUT I having a problem and I wanted to see if any Excel experts could help me out.

Here is the situation. From the first challenge to challenge 1418, the spreadsheet consist of 18,500 rows and 1421 columns. I need a macro that will only paste the formula I'm using into cells of value while not pasting them in blank cells. Currently the way I doing the pasting it paste the formula into every cell taking up valuable resources and memory.

Anyone know a solution to this?

Thanks,
Scott
07/19/2011 08:54:45 PM · #2
Can't you put zero or some placeholder value (-1) in the blank cells, and then just apply the formula to all of them?
07/19/2011 08:59:41 PM · #3
Originally posted by bvy:

Can't you put zero or some placeholder value (-1) in the blank cells, and then just apply the formula to all of them?

No because I need all the blank cells to stay blank because excel is starting to give me resource warning. Plus I'm not but about 2/3 of the way pasting formulas and the file sits at 47.2 MB. That's a large amount of data!
07/19/2011 09:01:29 PM · #4
Originally posted by bvy:

Can't you put zero or some placeholder value (-1) in the blank cells, and then just apply the formula to all of them?


I need a way to only apply the formula to the cell with value while not applying to the blank cells.
07/19/2011 09:10:49 PM · #5
This data isn't conducive to a pivot table. You need a database -- Access perhaps -- with a user and challenge table and then a scores table (or whatever measure you're working with) with the two foreign key references.

Sorry -- that doesn't answer your question. I don't work with macros, so I'm not going to be much help there.
07/19/2011 09:28:55 PM · #6
Originally posted by bvy:

This data isn't conducive to a pivot table. You need a database -- Access perhaps -- with a user and challenge table and then a scores table (or whatever measure you're working with) with the two foreign key references.

Sorry -- that doesn't answer your question. I don't work with macros, so I'm not going to be much help there.

I'm not to familular with Access. I do have Access 2007.

Thank you for your info.
Scott
07/19/2011 09:32:02 PM · #7
I'm guessing that you don't really mean post the formula into cells that have values, as it would wipe them out. Can you describe in more detail?
07/19/2011 09:48:32 PM · #8
Can you do a replace? I work in excel all day everyday, well, not all day, but it's close enough. Without seeing what you are talking about I'm having a hard time grasping what you mean.
I do agree with 21.gif Nobody, if you're putting the formula into cells that have a value, won't that delete that value?

I'm imagining that you have a column for every challenge, what are in your rows...Users?

I do agree that Access might be easier, but myself I prefer Excel over access most of the time.
07/19/2011 10:02:34 PM · #9
Not sure if this will work for you, but you can try it...
Column A:BBQ (mmmm BBQ!), you can adjust this as needed, but BBQ is column 1,421.
You need to adjust the Replacement:="=TODAY()" with your actual formula in place of the =TODAY().
You can then run it, it will just do a replace of everything in columns A:BBQ that are not blank.

Or, you can highlight all the data you want to replace, hit CTRL+F, goto the replace tab, in the Find What' part, you can enter *, then in the replace with, you can enter your formula.

I'm assuming if you use the macro, you will need to adjust it for your specific range, as if you have user names, or column headers/challenge names etc, it will replace them with your formula.

Let me know if this doesn't work, or if you need anything else.

Sub Macro1()
'
' Macro1 Macro
' Paste Formulas into non blank cells
'

'
Columns("A:BBQ").Select
Range("A1").Activate
Selection.Replace What:="*", Replacement:="=TODAY()", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
End Sub
07/20/2011 12:15:05 AM · #10
Originally posted by TheBaumPhotos:

Can you do a replace? I work in excel all day everyday, well, not all day, but it's close enough. Without seeing what you are talking about I'm having a hard time grasping what you mean.
I do agree with 21.gif Nobody, if you're putting the formula into cells that have a value, won't that delete that value?

I'm imagining that you have a column for every challenge, what are in your rows...Users?

I do agree that Access might be easier, but myself I prefer Excel over access most of the time.

Each column is a challenge.
Each Row is a User that has entered a challenge.
As of challenge 1417 I'm currently using 1423 columns and 18,245 rows. With each challenge it adds a column and x numbers of rows. The x number of rows are users that has not entered a challenge before.
I'm currently using a VBA workbook that Kirbic was kind enough to create for me for another project. It is a very complicated VBA script that does a lot.

1. I enter the challenge numbers that I want to get the data from.
2. It creates a scratch sheet, gathers the data, deletes the scratch sheet, and post the data on the data sheet.
3. Then it deletes closed accounts, changes usernames based on user Id if the user has changed there username.
4. It put the correct data in the correct user row according to challenge.

So I have a lot of blank cells where a user has not entered a challenge. I don't need a formula to place unwanted data in a blank cell such a "0" because it creates a resource and memory problem. There are more empty cells that data cells.
I need an addition added to the script to insert a second column using a formula to change the scores to percentile then delete the score column. Then goto the next challenge.

The way I'm doing it is not efficient causing problems.

07/20/2011 11:40:16 AM · #11
bump
07/20/2011 12:04:29 PM · #12
Hi Scott, can you just paste the piece of code that you are using to input the formula (on all cells)?

If the last rows are blank (assuming the users have not entered a challenge before), the the previous ones should be used, right?

It's difficult to see without the file, but I can try and help with the formula.

Cheers,
Joao
07/20/2011 03:24:16 PM · #13
Here is a screen shot of the excel spreadsheet I'm using.
Copyrighted_Image_Reuse_Prohibited_963259.jpg

What I'm looking for is a macro that will go by column (example column G:G which is challenge 2) convert the scores to percentile, skipping any blank cells. As you can see starting with the next challenge (column H) there are blank cells between scores that means that particular user did not enter a challenge. I want the macro to run that column as well but leave blank cells blank (enter no data) to free up resources and memory.

The macro could run one challenge then stop or run a specified range of challenges. Remembering with each challenge more users (rows) will be entered.

I think this is going to be impossible. I don't know why excel has so many columns and rows if it run out of resources.

Message edited by author 2011-07-20 15:37:28.
07/20/2011 03:36:30 PM · #14
This may be way off-base but have you considered just doing the analysis of the data using pivot tables instead of formulas. When I did a bunch of DPC stats earlier this year (Link)I just built a single spreadsheet containing the data for each image submitted with columns like Challenge ID, Submission Date, Rule Set, User Name, Average, Votes, etc. and then did all of the slicing and dicing analysis using the pivot tables. From there I could take sub sets of data to perform finer analysis too.

This is a general philosophy I use when using EXCEL in my daily work life. I try to keep the structure of the data in simple rows and columns like a database as opposed to a matrix (so to speak) and perform the analysis using the power of pivot tables.

My master file for the 2002-2010 data with pivots is too big to email successfully but I have the data only broken in to 4 seperate files if you would like to see one to review the structure I could send it to you. My 2011 file (through mid May only) might be small enough to email too if you want to take a look.
07/20/2011 04:48:36 PM · #15
Here's an example macro that might work. It will need modified a bit to fit your specific formula that you need entered in the cell, but it takes care of only putting the formula in the cells with a value.

Sub Skip_Blank_Cells()

Dim cval
Dim YourWksht
Dim YourWkbk

YourWkbk = "Your Workbook Name"
YourWksht = "Your Worksheet Name"

For Each cval In Workbooks(YourWkbk).Worksheets(YourWksht).Range(Worksheets(YourWksht).Cells(5, 13), Worksheets(YourWksht).Cells(18500, 13)) '18,500 is the end row

If cval <> "" Then
Workbooks(YourWkbk).Worksheets(YourWksht).Cells(cval.Row, cval.Column).Value = "=SUM(D" & cval.Row & ":Z" & cval.Row & ")" 'Or whatever your formula is
End If

Next cval

End Sub
07/20/2011 05:23:18 PM · #16
Originally posted by dahlin:

Here's an example macro that might work. It will need modified a bit to fit your specific formula that you need entered in the cell, but it takes care of only putting the formula in the cells with a value.

Sub Skip_Blank_Cells()

Dim cval
Dim YourWksht
Dim YourWkbk

YourWkbk = "Your Workbook Name"
YourWksht = "Your Worksheet Name"

For Each cval In Workbooks(YourWkbk).Worksheets(YourWksht).Range(Worksheets(YourWksht).Cells(5, 13), Worksheets(YourWksht).Cells(18500, 13)) '18,500 is the end row

If cval <> "" Then
Workbooks(YourWkbk).Worksheets(YourWksht).Cells(cval.Row, cval.Column).Value = "=SUM(D" & cval.Row & ":Z" & cval.Row & ")" 'Or whatever your formula is
End If

Next cval

End Sub


Brian may be onto something here, with the If condition.
Every time you import the data you have to do the percentile for each column of the 1423 column again?
That's a shame, unfortunately, but there might be a way.

In the macro above, the 18.500 lines can be replaced by a variable range (prob already Kirby fixed that).
And the If cval <> "" will put the formula only in the non-blanks, which already reduces memory usage.

You just need to be sure that it cval is pointing to the column you want to use the formula on.
And then you can do an IF inside another one, so you first run all rows, and when it reaches the last row it moves on to the next column, until a blank column is found.
07/20/2011 09:12:32 PM · #17
Thanks everyone. I trying to modify and see what works but I think this is going over my head.
I will keep trying and thanks for the advice and help.

Scott
07/20/2011 09:34:10 PM · #18
Use mysql and then use excel to generate an sql against it.
07/24/2011 02:56:39 AM · #19
Ok VBA and Macro experts, I have the formula but I need your help to see if a macro can be created to do the following.

When I run the macro it will start at the column I want and +1 till it gets to a blank column.
It needs to skip blank cells and only apply the following formula to cells within the column that has data.

=IFERROR(PERCENTRANK(Data!G$2:G$50000,Data!G2)*100,0)

As an example it needs to look from $G$2:$G$50000 and only apply the formula to the cell(s) with data, leaving the blank cells blank; no hidden formula or zeros - blank.

Does anyone know if this can be done.

Message edited by author 2011-07-24 07:53:22.
07/24/2011 07:55:04 AM · #20
Originally posted by dahlin:

Here's an example macro that might work. It will need modified a bit to fit your specific formula that you need entered in the cell, but it takes care of only putting the formula in the cells with a value.

Sub Skip_Blank_Cells()

Dim cval
Dim YourWksht
Dim YourWkbk

YourWkbk = "Your Workbook Name"
YourWksht = "Your Worksheet Name"

For Each cval In Workbooks(YourWkbk).Worksheets(YourWksht).Range(Worksheets(YourWksht).Cells(5, 13), Worksheets(YourWksht).Cells(18500, 13)) '18,500 is the end row

If cval <> "" Then
Workbooks(YourWkbk).Worksheets(YourWksht).Cells(cval.Row, cval.Column).Value = "=SUM(D" & cval.Row & ":Z" & cval.Row & ")" 'Or whatever your formula is
End If

Next cval

End Sub

I didn't see this this VBA, I will try to see if this works. Thanks Dahlin.
Scott
04/02/2012 03:10:40 PM · #21
Are you really sensing on blanks or do you mean nulls?

Anyway, you could try this:

Sub InForm()
Dim F as String, i, j As Long
F="=Your Formula"
For i=1 To 1421
For j=1 To 18500
If Cells(j,i)<>"" Then Cells(j,i)=F
Next j
Next i
End Sub

xladept

Message edited by author 2012-04-02 15:46:05.
Pages:  
Current Server Time: 03/24/2019 01:06:29 PM

Please log in or register to post to the forums.


Home - Challenges - Community - League - Photos - Cameras - Lenses - Learn - Prints! - Help - Terms of Use - Privacy - Top ^
DPChallenge, and website content and design, Copyright © 2001-2019 Challenging Technologies, LLC.
All digital photo copyrights belong to the photographers and may not be used without permission.
Proudly hosted by Sargasso Networks. Current Server Time: 03/24/2019 01:06:29 PM EDT.