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

Harminoff: Oct 24, 2005; ð½

I have a spreadsheet with two columns showing order numbers. One column(A) is all open orders from yesterday, the next(B) is all open orders from today.

Is there a way that I can have it so when I input the data into column A and B it extracts the unique values in column B to column C?

Right now I'm using conditional formatting to highlight the unique values in red and then sort by red however I'd like to automate it if possible.

# ¿ Dec 8, 2017 23:22

Adbot: ADBOT LOVES YOU

# ¿ May 11, 2024 08:32

Harminoff: Oct 24, 2005; ð½

SymmetryrtemmyS posted:

Is this what you want to do?

In C2: =IF(ISNUMBER(MATCH(B2,A:A,0)),"",B2)

Thanks for the help!

This does get rid of the duplicates however leaves empty cells where they were. Any way to just put them in a column with no spacing?

# ¿ Dec 11, 2017 15:43

Harminoff: Oct 24, 2005; ð½

Just a general question but I'm just starting to learn VBA so that I can automate some tasks that take me 30+ minutes to complete each day. One portion I need to add formulas to 5 columns, about 1k lines and it takes a few minutes to complete. I do hide what's happening to speed it up a bit. Is VBA just really slow with pasting a lot if data into cells?

# ¿ Oct 23, 2018 02:07

Harminoff: Oct 24, 2005; ð½

mystes posted:

Interacting with Excel stuff through the object model is slow so you want to minimize the number of times you do it.

If you are inserting the exact same formula in lots of cells you can just set it as the formula for all the cells as a single range at the same time.

Otherwise you can first create an array with all the different formulas and set it at once but this is slightly more annoying.

Hmm the base of the formula is the same, but it changes to reference a different cell each time. I'll look into arrays and see if that'll help at all as there are a few times I'll need to be doing this with slightly different formulas. It wouldn't be terrible if it just did it all in the background but it seems to lock up any other workbook I'm using so it makes my machine worthless during the run time.

# ¿ Oct 23, 2018 02:31

Harminoff: Oct 24, 2005; ð½

Thanks all! Setting it as a range formula did the trick. I had a loop that would paste it to each cell, don't the range formula makes it almost instant.

Thanks again!

# ¿ Oct 23, 2018 15:50

Harminoff: Oct 24, 2005; ð½

Back again. Is there really no way to have a cell be truly blank when using a formula? For example

quote:

Range("B1:B" & lRow).Formula = "=IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE),"""")"

Will still return a blank value if nothing is found in column 2. I did find this, and it does work, it just seems like a bit much to a simple problem.

quote:

With ActiveSheet
.AutoFilterMode = False
With .UsedRange
.AutoFilter
For Col = 1 To .Columns.Count
.AutoFilter Field:=Col, Criteria1:=""
.Offset(1).Columns(Col).ClearContents
.AutoFilter Field:=Col
Next Col
End With
.AutoFilterMode = False
End With

# ¿ Oct 25, 2018 04:18

Harminoff: Oct 24, 2005; ð½

So I have part of a macro that I need to run on the 15th each month, or next working day. Is there an easy way to do this?

Right now I have it check if today is the 15th then run, or if it's either the 16th or 17th and a Monday then run. However doing it this way wouldn't count for holidays, and I'd have to add a bunch of checks to work around holidays.

I'm thinking I should be able to use the workday function, but can't seem to figure out how to apply it to this scenario.

# ¿ Nov 15, 2018 17:54

Harminoff: Oct 24, 2005; ð½

mystes posted:

Basically you just want to run your thing on the date workday(dateofthe15th-1,1) right?

It looks like you will also need to have a list of holidays in your worksheet and use that as a parameter as well, though.

So like this? Seems to work!

quote:

Sub test2()

Dim datetest As Date

Dim holidays As Range

datetest = Format(Date, "mm") & "/15/" & Format(Date, "yy")

Set holidays = Range("A1:A2")

If Date = WorksheetFunction.WorkDay(datetest - 1, 1, holidays) Then

MsgBox ("test")

End If

End Sub

# ¿ Nov 15, 2018 19:30

Harminoff: Oct 24, 2005; ð½

I have a few users that work in duplicate worksheets but different data sets.

For example :

Order number | date worked | note

Is it feasible to import their data to a central worksheet?

I'd eventually like to pull and push data to this central worksheet. Will I run into massive slowdown from accessing the worksheet frequently to pull and push this data?

# ¿ Feb 14, 2019 02:33

Harminoff: Oct 24, 2005; ð½

I've been learning vba during my free time at work over the last year to automate some tasks and now a few other teams learned that I know vba so now I'm just coding vba all day, with the goal of automating the formatting of about 20 different workbooks.

The formatting used to take a few hours to do, and now it takes minutes at most.

I should ask for some sort of raise right? My biggest concern is that if any of them been I'm the only one that would be able to fix it. Job security I guess.

# ¿ Jun 8, 2019 02:48

Harminoff: Oct 24, 2005; ð½

Yeah that's true. We just use it to create categorized spreadsheets of data that we pull out of sap. Manually formatting it which they have been doing for the last 5+ years just takes forever and I'm amazed some other solution hasn't already been done.

With that said, I am learning Blue Prism in the next few weeks so that should be able to automate a bit more of the work as well.

Hopefully that's something useful to add to my resume.

# ¿ Jun 10, 2019 19:12

Harminoff: Oct 24, 2005; ð½

Probably not the best way, and phone posting, but try this.

Put starting date in f1 ending date in f2 or adjust as needed.

=Sumifs(c:c,b:b,">="&f1,b:b,"<="&f2)

# ¿ Nov 6, 2019 22:19

Harminoff: Oct 24, 2005; ð½

Yeah just loop though it.

Can use this too, just change the range to the needed column.

quote:

Sub test()

    Dim c As Object



    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)

        If c < "3/1/2019" Then

            c.Value = DateAdd("yyyy", 1, c)

        End If

    Next

End Sub

# ¿ Nov 12, 2019 18:59

Harminoff: Oct 24, 2005; ð½

Kind of have an interesting issue. I'm working on making a Dashboard and want it to look nice. I'm trying to set the background cell color to blue, however when the pivot tables resize it resets the cell colors to white. Is there any way I can keep them blue?

Or, is there a way that I can force the height of a pivot table? Adding a scrollbar would be fine.

# ¿ Nov 22, 2019 18:43

Harminoff: Oct 24, 2005; ð½

hmm trying that seems to change the pivot table themes and colors, but just the worksheet colors will still change if the pivot table is resized. From say 15 rows to 10 rows, the 5 rows will now be white instead of blue.

Edit: Found kind of a dumb workaround. I took a screen snip of some of the blue, and then just loaded that as a background image. Seems to work.

Harminoff fucked around with this message at 20:10 on Nov 22, 2019

# ¿ Nov 22, 2019 20:03

Harminoff: Oct 24, 2005; ð½

Does anyone know of a way to filter data by wildcard in power query?

For example, I want to filter out

Filter this out
Filter that out

I'd think you could do
Filter*out

To catch both, but it doesn't seem to work?

# ¿ Feb 6, 2020 17:02

Harminoff: Oct 24, 2005; ð½

LawfulWaffle posted:

Triple posting, sorry, but I have something more manageable than the previous questions (I figured out how to use macros so I made an easy way to fix that previous error).

I have a growing list of ZIP codes on applications, and I have a short, static list of approved ZIP codes. I want to compare the big list to the small list and pull out data from the big list when it finds a ZIP code that's not approved. I don't know a good way to do this, especially not in a way that will result in a small list instead of a long list with a bunch of negative results that need filtered out. I'm talking a handful of bad ZIPs out of 300, and that number grows by 100+ each day.

These last few days I've probably made 40 Google searches starting with Excel but I can't seem to find a good answer to this problem.

Sounds like you can just the =countif() function for this. Then just filter for 0 in that column to pull out any rows with zipcodes it can't find.

# ¿ Apr 6, 2020 19:54

Harminoff: Oct 24, 2005; ð½

You should be able to query the first table (look into power query) and then just use refresh all to update it.

# ¿ Apr 8, 2020 19:32

Harminoff: Oct 24, 2005; ð½

Yeah looks to be way more complex than it should be. Found this

https://www.youtube.com/watch?v=6PcF04bTSOM

# ¿ Jul 23, 2020 02:36

Harminoff: Oct 24, 2005; ð½

It also only works with excel online files, mostly with just tables too if I remember correctly.

I have to do some dumb poo poo like have excel scrape data from multiple workbooks using vba, compile it and send a report using vba yo email, then upload some stats to SharePoint using vba so that I can create power bi dashboards using that data. Pretty crazy and I'm sure there is a better way to do it. Hopefully office script can help with some of it.

# ¿ Sep 13, 2020 21:06

Harminoff: Oct 24, 2005; ð½

Have you tried anything with Power Query yet?

# ¿ Dec 16, 2020 19:34

Harminoff: Oct 24, 2005; ð½

Should be able to import with power query and than refresh that to get updated data. No need to copy/paste

# ¿ Dec 30, 2020 23:30

Harminoff: Oct 24, 2005; ð½

Looks like this would work

https://excel.officetuts.net/en/formulas/index-match-or-vlookup-to-return-multiple-values

Though yeah seems way easier to just use a pivot table and filter.

# ¿ Feb 23, 2021 17:41

Harminoff: Oct 24, 2005; ð½

Alternatively throw it into power bi and do it in like 3 clicks. Just use the free version and and send a screenshot if you don't have a premium license.

I used to make a bunch of visuals and dashboards in excel, but gently caress thatm power bi is just so much easier at that stuff.

# ¿ May 8, 2021 02:33

Harminoff: Oct 24, 2005; ð½

I'd look into the Filter function if you have a newer version of excel. It can filter and spill all the data where you need it.

https://www.youtube.com/watch?v=4Fgb8qp0NxU

# ¿ Jul 21, 2022 00:36

Harminoff: Oct 24, 2005; ð½

Rakeris posted:

Anytime I see these new functions, I get annoyed that my work has not upgraded us to desktop O365 yet (we can only use the web versions), so many nice changes in excel.

drat so no xlookups? I'd dread having to go back to vlookups and index/match

# ¿ Jul 21, 2022 05:36

Harminoff: Oct 24, 2005; ð½

Don't know how new it is, but flash fill (cntrl e) is pretty amazing

https://dollarexcel.com/what-does-ctrl-e-do-in-excel/

# ¿ Jul 21, 2022 14:06

Harminoff: Oct 24, 2005; ð½

Learning macros/vba doubled my income. Do it

# ¿ Jul 28, 2022 23:27

Harminoff: Oct 24, 2005; ð½

Is anyone watching Excel Esports on ESPN?

https://www.youtube.com/watch?v=x1RVNGDSdw4

# ¿ Aug 10, 2022 17:58

Harminoff: Oct 24, 2005; ð½

Understandable. Syncing slicers in excel is a pain in the rear end. That's trivial to do in power bi.

# ¿ Aug 11, 2022 02:42

Harminoff: Oct 24, 2005; ð½

The easiest way would be to just put it in workbook activate/deactiviate

Put this in the ThisWorkbook module

Private Sub Workbook_Activate()
Application.MoveAfterReturnDirection = xlToRight
End Sub

Private Sub Workbook_DeActivate()
Application.MoveAfterReturnDirection = xlDown
End Sub

This way, whenever you click onto that workbook it'll change it to the right direction. When you click into another workbook, it'll change it to down.

# ¿ Oct 13, 2022 17:10

Harminoff: Oct 24, 2005; ð½

Can you try putting the text formated as you'd like it and then press Ctrl+e (it should be flash fill but it's only available in the newer versions of excel)

# ¿ Oct 14, 2022 03:27

Harminoff: Oct 24, 2005; ð½

I'm just getting into sap gui scripting with excel/vba to automate a bunch of our work. About to push my first build to production!

It's crazy how well it works and how fast it is. It's taking 8 hours of manual work down to less then an hour!

Lots of trial and error though. Not a lot of documentation so kinda hodge podged it all together.

# ¿ Nov 19, 2022 14:06

Harminoff: Oct 24, 2005; ð½

Xerol posted:

I have a sheet of data, which I am able to filter(unique()) on to get a list of names. I want each of these names to be repeated 3 times in the output, so e.g. if my formula is in A2 I want Data!A2 in A2, A3, A4; Data!A3 in A5, A6, A7, and so on. Is there any way to do this without macros/VBA?

e: Just putting the output in A2, A5, A8, and so on is also "good enough" for what I want this for.

I asked chatgpt exactly what you asked, and it returned this

=INDEX(Data!A:A, ROUNDUP(ROW()/3,0))

which does work. drat that tech is crazy.

quote:

I have a sheet of data, which I am able to filter(unique()) on to get a list of names. I want each of these names to be repeated 3 times in the output, so e.g. if my formula is in A2 I want Data!A2 in A2, A3, A4; Data!A3 in A5, A6, A7, and so on. Is there any way to do this with a formula?

quote:

Yes, you can use the INDEX and ROW functions in combination to repeat each name from your list 3 times in the output. Here is an example formula you can use:

=INDEX(Data!A:A, ROUNDUP(ROW()/3,0))

You would start by entering this formula in cell A2, then copy and paste it down the column for as many rows as you need. This formula uses the INDEX function to pull the value from the first column (A) of your Data sheet, and the ROW function to determine the current row number. The ROUNDUP function is used to divide the row number by 3 and round up to the nearest whole number, which results in each name being repeated 3 times in the output.

# ¿ Jan 24, 2023 21:49

Harminoff: Oct 24, 2005; ð½

Lib and let die posted:

Small Excel Questions Not Worth Being Answered by a Human

It's crazy how good it is, like just trying AG3's question gives working results.

quote:

Yes, it is possible to use VBA to accomplish this task. You can use the "Find" method to search for the specific number or text string in the specified column of the other sheet, and then use the "EntireRow" property to copy the contents of the entire row containing that string to the current row. Here is an example of how you can accomplish this using VBA:

Copy code
Sub ImportRow()
Dim searchString As String
Dim searchRange As Range
Dim foundCell As Range

searchString = ActiveCell.Value
Set searchRange = Sheets("Sheet2").Range("A:A")
Set foundCell = searchRange.Find(searchString)

If Not foundCell Is Nothing Then
foundCell.EntireRow.Copy ActiveCell.EntireRow
Else
MsgBox "The search string was not found."
End If
End Sub

You can call this subroutine in a worksheet's code-behind, for example, by using a command button to run the macro.

Of course, this one would need a little edit to see the other workbook instead of looking up the current one, but the code does work and is a good starting point.

Here is what I get by editing the quesiton a bit

quote:

Sub ImportRow()

'Declare variables
Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim searchValue As String
Dim searchRange As Range
Dim foundCell As Range

'Set the workbooks and worksheets
Set wb1 = ThisWorkbook 'The current workbook
Set wb2 = Workbooks("Book2.xlsx") 'The other workbook
Set sh1 = wb1.Sheets("Sheet1") 'The current sheet
Set sh2 = wb2.Sheets("Sheet1") 'The sheet in the other workbook to search

'Get the search value from cell A1 in the current sheet
searchValue = sh1.ActiveCell.Value

'Set the range to search in the other sheet
Set searchRange = sh2.Range("A:A")

'Search for the search value in the search range
Set foundCell = searchRange.Find(searchValue)

'Check if the search value was found
If Not foundCell Is Nothing Then
'Select the entire row of the found cell
foundCell.EntireRow.Select
'Copy the selected row
Selection.Copy
'Go to the current sheet and select the current row
sh1.Select
sh1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
'Paste the copied row
Selection.PasteSpecial xlPasteValues
Else
'If the search value was not found, display a message
MsgBox "The search value was not found in the other sheet."
End If

End Sub

you could then use a shortcut and press that to bring the data over.

Harminoff fucked around with this message at 23:11 on Jan 24, 2023

# ¿ Jan 24, 2023 23:08

Harminoff: Oct 24, 2005; ð½

Does this do what you are looking for? Put it in sheet1 instead of its own module.

I have two sheets, one called sheet1, and the other called data. Data has things like

123|tom|bob|roger

in sheet1, if I type 123 into cell a1, it will then become 123|tom|bob|roger

code:

Private Sub Worksheet_Change(ByVal Target As Range)

 

    If Target.Column = 1 Then
	Dim searchValue As Variant
	Dim searchRange As Range
	Dim foundRow As Range

	searchValue = Target.Value
	
	Set searchRange = Worksheets("Data").Columns(1)
	Set foundRow = searchRange.Find(what:=searchValue, LookIn:=xlValues, lookat:=xlWhole)

	If Not foundRow Is Nothing Then
		Application.EnableEvents = False
		foundRow.EntireRow.Copy Range(Target.Row & ":" & Target.Row)
		Application.EnableEvents = True
	End If
End If

End Sub

# ¿ Feb 15, 2023 16:57

Harminoff: Oct 24, 2005; ð½

Sounds like it wasn't saved as an xlsm but just xls.

# ¿ Jul 13, 2023 16:23

Harminoff: Oct 24, 2005; ð½

Just a heads up but vlookups have been replaced with the much better xlookup. Soo much easier to use and can return value on either left or right

# ¿ Sep 9, 2023 16:08

Adbot: ADBOT LOVES YOU

# ¿ May 11, 2024 08:32

Harminoff: Oct 24, 2005; ð½

Get power bi, it's free. Then you can just use power query to join them all, and make a really slick looking dashboard.

# ¿ Feb 8, 2024 07:07

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