Started by Sophia Newman6 · · 👁 68 views · 1.6K replies
…
wiredcobra46 said:how do I make that spacing—you know, actually get COLUMNS? I get how to do rows—just hit enter... THANKS SO MUCH FOR THE HELP!!! YOU'RE SAVING MY LIFE! 🙂
Gregory Rogers20 said:Any clue how to fix this so it actually works with decimals?
Benjamin Wilson7 said:For the first round, she nested an IF (using AND) and solved the issue. Now we’ll see if he actually understands how function nesting works.
Sarah Brown86 said:I have no idea how to make a Macro command work
Kyle Johnson2 said:Here is the second part of the code, picking up right where the first bit left off.
Code:Range("D20").Select
Selection.Copy
Range("D21").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C21").Select
ActiveCell.FormulaR1C1 = "5"
Range("D21").Select
Selection.Copy
Range("D22").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C22").Select
ActiveCell.FormulaR1C1 = "17"
Range("D22").Select
Selection.Copy
Range("D23").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C23").Select
ActiveCell.FormulaR1C1 = "19"
Range("D23").Select
Selection.Copy
Range("D24").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C24").Select
ActiveCell.FormulaR1C1 = "14"
Range("D24").Select
Selection.Copy
Range("D25").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C25").Select
ActiveCell.FormulaR1C1 = "12"
Range("D25").Select
Selection.Copy
Range("D26").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C26").Select
ActiveCell.FormulaR1C1 = "11"
Range("D26").Select
Selection.Copy
Range("D27").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C27").Select
ActiveCell.FormulaR1C1 = "24"
Range("D27").Select
Selection.Copy
Range("D28").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C28").Select
ActiveCell.FormulaR1C1 = "20"
Range("D28").Select
Selection.Copy
Range("D29").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C29").Select
ActiveCell.FormulaR1C1 = "4"
Range("D29").Select
Selection.Copy
Range("D30").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C30").Select
ActiveCell.FormulaR1C1 = "11"
Range("C31").Select
select ActiveWindow.SmallScroll down : = -18 range
Range("D2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,VLOOKUP(RC[-1],R1C6:R6C7,2,TRUE),"""")"
Range("D2").Select
Selection.AutoFill Destination : = range("D2:D30"), Type:=xlFillDefault
Range("D2:D30").Select
Range("A1:D30").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("F1:G6").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("J2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R2C4:R39C4,R[-1]C)"
Range("J2").Select
Selection.AutoFill Destination : = range("J2:M2"), Type:=xlFillDefault
Range("J2:M2").Select
Range("A1:D30").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial paste := xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
Range("A1:D1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("$A$1:$D$30").AutoFilter Field:=4, Criteria1:=Array("2", _
"3", "4", "5"), Operator:=xlFilterValues
End Sub
is the "program" I think would handle the task, at least in my estimation
Kyle Johnson2 said:@Gregory Rogers20/">@@Gregory Rogers20, hey there!
I was honestly hoping you might put in a little bit of elbow grease and try to build something yourself first! If that wasn't possible, perhaps you could have shared a sample spreadsheet of what you're working on—using some dummy data, of course—to show us exactly where you're hitting a wall.
But no worries at all, I've gone ahead and whipped up an example based on how I interpreted your request!
On the "database" Sheet, you'll find a full roster of players, which includes a serial number, their handicap (as a number), and their names.
Once you've finished entering everyone—especially after inputting those handicap numbers—the rows will automatically sort themselves, bringing the corresponding names along with them. Every time you update a handicap and hit enter, that automatic sorting kicks back in immediately.
Then, on the "groups" Sheet, you simply input the MIN and MAX values for each group, and the data filters itself according to those criteria.
Since I'm certainly not a mind reader and can't know exactly how you envisioned your layout, please feel free to tailor this to fit your specific needs! 🙂
You can download the file via this link: Automatic Sorting with MIN and MAX
The password to unpack the file is:
Code:www-ic-ims-us
Sarah Brown86 said:THANKS 🙂 for working on this... although I actually managed to pull it off without a Macro by using an IF function 🙂 ugh, Excel... just hoping to get a passing grade 🙂 fingers crossed
Gregory Rogers20 said:Sending over two files—cleaned things up a bit and made it way clearer.http://www.mediafire.com/file/hl2y5uauwarl7ia/hdcp.zipBtw, if you're going with file2 (using VB), just grab the group table from file1.
Kyle Johnson2 said:👎
My apologies! I only provided you with the basics, but the formulas are right there for you to work with decimals. If you take some time to study them, you'll be able to customize everything exactly the way you want it.
Best regards,
Gregory Rogers20 said:For the hundredth time: using a formula is just stupid because it doesn't actually delete the rows—it just leaves "NN" everywhere. Can we handle decimals in Visual Basic?
jaderider11 said:Basically, I need a spreadsheet that takes all the data where the code ends in 1 and puts it into one table, then everything ending in 2 goes into another, and so on up to 10.
Kyle Johnson2 said:Goodness, you certainly are fired up! It feels as though if you were any more intense, you might actually combust right here on the forum. If I may be so bold as to offer a gentle critique, it seems to me that perhaps you didn't quite pull out all the stops on this one; there was a certain lack of meticulous effort in your approach that I simply couldn't help but notice. Personally, I found the entire process to be incredibly smooth, and I managed to get everything running perfectly using the exact formulas provided by Benjamin Wilson7 and Nicholas King. In fact, I must say, I found that method to be significantly more straightforward and far less cumbersome than wrestling with VBA code!
You actually provided an entirely blank file that you built using the template I sent over! It’s quite a curious thing, really, because you haven't included a single formula to back up your rather bold claim that this simply cannot be achieved through formulas.
Take a look at what Gregory Rogers20 is up to!
I truly believe there is nothing more rewarding than lending a helping hand when someone hits a roadblock, but one must also learn to exercise a bit of independent critical thinking. In my honest opinion, it felt as though you hadn't quite put in the necessary effort to explore the solution on your own first.
Please understand that this forum isn't a place to drop off your homework or demand that someone hands you a finished product on a silver platter! Our community exists to provide a helping hand when you hit a metaphorical brick wall or find yourself stuck on a tricky step. If your goal is simply to obtain a completed project without doing the legwork, then you aren't looking for a forum; you should instead seek out a professional freelancer, explain exactly what you need, and pay them for a turnkey solution. We are here to learn together, not to do the work for one another!
To truly demonstrate my point, I am providing you with this comprehensive tutorial, complete with every single illustrative step and all the necessary macro commands, just to prove that achieving something of this caliber requires genuine effort and dedicated labor! You have been handed every possible guideline and link available, yet it seems you haven't quite put in the work yourself. At this stage, the ball is entirely in your court; it is up to you to take these instructions, copy them accurately, and apply them directly to your own file.
- Is there anyone else out there who finds themselves constantly wrestling with massive datasets, trying to isolate just the most extreme values? I was working on a project the other day—much like one of those intense quarterly audits you might see at a firm like Deloitte—where I found myself staring at a spreadsheet that felt more like an endless labyrinth than a useful tool! I desperately needed a way to automatically grab everything sitting between my minimum and maximum values without manually clicking through thousands of rows. It’s one of those tasks that feels simple in theory but becomes a total headache when you're actually in the thick of it. I would love to hear how you all handle this! Does anyone have a preferred method for automating a sort and copy routine that targets that specific range between a MIN and MAX value? Whether you're using some clever formulas or a bit of automation, I am all ears!
Greetings to everyone! I hope you are all having a truly wonderful day.
Gregory Rogers20 said:Thank you so much, I will certainly give that a shot.
Gregory Rogers20 said:Don't get mad, please, but I did everything I could to make this work.
