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

me your dad: Jul 25, 2006

I have a spreadsheet with the following bit of data:

The user of the spreadsheet will enter data under the Actual column for Calls, Sales, and Hours. CPH and SPH are just Calls Per Hour and Sales Per Hour. This part is already figured out.

What I need:

The third column is Year To Date. These cells should reflect the number entered into the Actual column. This is of course, easy enough.

However, each week, this worksheet will be duplicated and the YTD column will need to be a running total based on the previous week. Each week the data in the Actual column will change.

Can someone help me out in getting a formula together for this? I'd rather not do it in VBA but I can if necessary.

# ¿ Nov 23, 2009 17:58

Adbot: ADBOT LOVES YOU

# ¿ Apr 27, 2024 23:17

me your dad: Jul 25, 2006

TheAngryDrunk posted:

My first inclination would be to talk you out of doing it on multiple sheets.

Failing that, however, you can sum cells across worksheets. For example, this formula =SUM(Sheet1:Sheet3!A1) will sum all of the A1 cells on the worksheets that are in between Sheet1 and Sheet3 (inclusive).

Thanks - that's a help. What reason is there for not doing it across multiple sheets? The goal I think was to have a sheet for each week.

# ¿ Nov 24, 2009 03:47

me your dad: Jul 25, 2006

I have an Excel 2003 worksheet to go through, containing about 54,000 rows. The cells contain email addresses. I need to retrieve only cells which contain a certain string within the domain name.

For example, a selection of cells may look like this:

code:

claude@this.abcd.net
andrew@domain.com
gene@123.abcd.net
dean@here.com
dave@jv.d.abcd.net
glen@abcd.net

I want to pull only those email addresses that contain any variation of abcd.net and copy them to another column.

I really can't figure out where to begin with this.

# ¿ Jul 6, 2011 20:34

me your dad: Jul 25, 2006

Aredna posted:

If your data starts in cell A1, put this in B1. It will return true for the addresses you want to keep: =not(iserror(find("abcd.net",A1)))

Note: This will also find those that have "abcd.net" in the mailbox name as well, but I'm assuming it won't be in your data. With necessary error checking it's a bit more complicated, but this only finds the ones with "abcd.net" after the @ sign: =if(not(iserror(find("@",A1))),not(iserror(find("abcd.net",A1,find("@",A1)))))

Thank you so much for the fast help - this worked perfectly!

# ¿ Jul 6, 2011 20:51

me your dad: Jul 25, 2006

When saving a file as .csv, why does it ask if you want to save the changes to file you just saved?

For example, I open a new workbook and populate a worksheet. I save it as csv. First, it tells me the format doesn't accept multiple sheets. I say ok. Next it tells me some features may not be compatible with csv. I say ok. Then, after the file has saved and I try to close the window, I get "Do you want to save the changes you made to [filename]?" I always click no, because otherwise it leads to a loop, and I've confirmed the changes have been made after saving.

Just curious.

# ¿ Mar 14, 2012 20:47

me your dad: Jul 25, 2006

I'm posting this for my wife. She has two ranges of numbers, and needs the MIN of the two ranges, excluding any cell with "0".

She says:

I need to find the MIN for cells B2:B26 and B31:B54 (not separately but as a collective)
I do not want the MIN considered in the formula for any cell that shows �0�

I keep it this way because the report has to show 2 separate groups of data but the stats need to be collective for MIN, MAX, MEDIAN, and MODE

This is the sample data in B2:B26 and B31:54

Any idea how to do this?

code:

# ¿ Mar 14, 2013 19:22

me your dad: Jul 25, 2006

I have a column containing email domains. I want to isolate the TLD into a column (.com, .org, and so on).

I normally do this with =mid(b2,find(".",b2)+1,3) but some of the domains have multiple periods, such as 104acv.skc.army.mil (we deal with government and military data). Another domain may only have one period like gmail.com, and another may have two periods, such as us.army.mil.

Is there a way to find the last occurrence of the period, and copy out the characters following it?

# ¿ Jul 24, 2013 18:51

me your dad: Jul 25, 2006

Thank you both - I'll give it a shot to see what I can make happen.

Edit - The formula above worked perfectly - thanks again!

me your dad fucked around with this message at 19:56 on Jul 24, 2013

# ¿ Jul 24, 2013 19:44

me your dad: Jul 25, 2006

In Excel 2013, I am suddenly seeing something weird.

I'm doing a very simple formula - dividing two cells. But when I click on the cells to divide, I get this:

=[@Column6]/[@Column5]

This should be:

=F54/E54

What did I do to cause this to happen?

me your dad fucked around with this message at 18:57 on Nov 22, 2013

# ¿ Nov 22, 2013 18:54

me your dad: Jul 25, 2006

DukAmok posted:

Somewhere in your General options or preferences there's a "R1C1 Reference Style", sounds like you switched over.

Thanks - I figured it out. I had started to make a pivot table earlier and I think something in my pivot settings caused it. Disabling the pivot table seemed to work.

Another question, this time about data structure:

I have a spreadsheet tracking email sends. Included are typical things like Opens, Bounces, Open Rate, Delivery Rate, and so on. This means one value per metric. However, I've been requested to add the lists which were used for each send. Each send may go to multiple lists, meaning multiple values per metric "Lists".

How would you structure this? If a send has 10 lists it went to, I want to avoid stacking them row by row, causing excessive space between other rows. I considered using a dropdown list so they could be retrieved if desired.

Would there be a better way?

# ¿ Nov 22, 2013 21:58

me your dad: Jul 25, 2006

EDIT: I believe I have it figured out. I think I can use an "if" statement for the blank cell and say "if cell value = blank, concatenate without suffix, else concatenate with suffix.

Concatenation question.

I have a file which has the following fields:

code:

AsstFirst	AsstLast	CEOfirst	CEOlast		CEOsuffix
Bob		Dobbs		Ivan		Stang		Jr.
Jane		Lee		Nancy		Smith		Ph.D.
Mort		Crunder		Michael		Crabs		
Barbara		Johnson		Missy		Miller		MD

These fields will be concatenated into a sixth column:

=CONCATENATE(A2," ",B2," ","and"," ",C2," ",D2," ",E2,",")

and used as a variable within the salutation of an email blast so it will look like:

Dear %%ConcatenatedNames%%

Which results in:

Dear Bob Dobbs and Ivan Stang Jr.,

The problem is that for lines like row 3, there is no suffix, so a blank will appear, resulting in a space before the comma in the salutation:

Dear Mort Crunder and Michael Crabs ,

Is there a conditional way to get around this?

me your dad fucked around with this message at 21:56 on Jan 24, 2014

# ¿ Jan 24, 2014 21:02

me your dad: Jul 25, 2006

Old James posted:

Looks like you already have a solution, but here's another way to do it.

=TRIM(CONCATENATE(A2," ",B2," ","and"," ",C2," ",D2," ",E2))&","

This is a cool way to do it. I'll probably use this method to familiarize myself with another technique. Thanks

# ¿ Jan 25, 2014 17:41

me your dad: Jul 25, 2006

Old James posted:

Looks like you already have a solution, but here's another way to do it.

=TRIM(CONCATENATE(A2," ",B2," ","and"," ",C2," ",D2," ",E2))&","

So this ended up working better than my original formula but I don't understand how it works.

What does the &"," do at the end of the formula?

# ¿ Jan 30, 2014 18:33

me your dad: Jul 25, 2006

I just wanted to say thanks for all the help in this thread. The selfless guidance from Excel nerds has no doubt made many a person seem very smart in the eyes of coworkers.

:glomp:

# ¿ Feb 3, 2014 01:16

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

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

me your dad: Jul 25, 2006

I've got a file containing the following type of data. Each element exists in its own cell. I'm trying to extract the first name, last name, suffix, and email address.

code:

FirstName LastName Suffix
CompanyName
Division
Street1
Street2
City, State, Zip
Phone: (123) 456-7890
[email]email@domain.com[/email]




FirstName LastName Suffix
CompanyName
Division
Street1
Street2
City, State, Zip
Phone: (123) 456-7890
[email]email@domain.com[/email]

FirstName LastName
CompanyName
Street1
City, State, Zip
Phone: (123) 456-7890
[email]email@domain.com[/email]




FirstName LastName Suffix
CompanyName
Street1
City, State, Zip
[email]email@domain.com[/email]

It's tough to see here, but some of the blocks are separated by two empty rows, and some are separated by one empty row. This is the first hurdle. The second hurdle is that the number of rows containing the data isn't uniform. Some blocks have five rows, some have eight, some have seven. It depends on the length of their mailing address.

But, the name of the person is always the first line of each block.

I'm thinking it may be ~~best~~ ~~easiest~~ possible to:

Create a macro to go row by row, extracting data from the first populated cell after an empty cell. Each element (First Name, Last Name, Suffix, Email Address), should be copied into their own cells, row by row.

I'd then need to skip down until an email address is found, and extract that into the end of the same row containing the full name data.

Does that sound doable? Might there be a smarter way to go about it?

me your dad fucked around with this message at 17:01 on Jun 3, 2014

# ¿ Jun 3, 2014 16:58

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

me your dad: Jul 25, 2006

Okay. It's ~~4:01~~ 4:07 p.m. ET. Maybe someone will check this thread soon :ohdear:

I've got a logistical problem to work out.

I have five workbooks. Lets call one of them MASTER workbook. The others are 1-4.

MASTER workbook has 17,296 rows, and three columns for Email Address, First Name, Last Name.

Workbooks 1-4 have varying rows, and three columns for Email Address, First Name, Last Name.

I need to take any row from 1-4 which exists in MASTER workbook and remove them from MASTER workbook.

I can consolidate workbooks 1-4 into one workbook to make the comparison easier.

EDIT - I think I've got it, but I'd appreciate someone telling me I'm right.

I merged Workbooks 1-4 into a single file, and then pasted the contents in columns D, E, F of the MASTER workbook.

The result was:

code:

A		B		C		D		E		F
First Name      Last Name       Email		First Name	Last Name	Email

**I Removed headers before proceeding with the next step**

Then I inserted a column in the middle (which is why I reference column G) and used this formula:

=NOT(ISNA(VLOOKUP(C1,$G:$G,1,FALSE)))

That returned "True" for a bunch of them, and spot checks indicate they were in both columns. I deleted the TRUE rows.

This should do it, right?

me your dad fucked around with this message at 22:17 on Jul 18, 2014

# ¿ Jul 18, 2014 21:07

me your dad: Jul 25, 2006

What would the formula be to get a number based on a percentage?

To clarify: I have a report for an email send, which shows TOTAL opens by recipients, and an OPEN RATE percentage. The Open Rate however, is based on UNIQUE opens, which is not shown.

The Open Rate formula is [Unique Opens] / [Delivered Emails]

So if 50 people opened an email which was sent to 100 recipients, it would have a 50% open rate.

The email was delivered to 8,595 recipients. My report says 2,739 opens but the open rate is 22.33%. This percentage is based on the unique open rate, which is not shown.

Based on these numbers, how would I get the unique opens?

# ¿ Jul 1, 2015 15:09

me your dad: Jul 25, 2006

fosborb posted:

I feel like I'm missing something here but....
8,595 delivered emails * 22.33% open rate = 1919 unique opens

I'm not very good at math :blush:

Thank you.

# ¿ Jul 1, 2015 15:25

me your dad: Jul 25, 2006

gently caress my life :suicide:

My boss has asked me to merge two Workbooks together. One workbook as 130 columns, the other 129. Besides the difference in columns, some column headers have different labels. So the merged file will have more than 130 columns.

How would you all tackle this? It seems like it's going to be a miserably manual process but I just can't wrap my head around an easy (or least painful) way of doing it. I've been asked to turn it around by COB tomorrow.

My first approach is to simply begin copying each column one by one. If I find a column that doesn't match oh gently caress this is going to suck.

If it matters,

One of the workbooks is 139 rows
The other is 1,950 rows

Thoughts? I've never had to do anything with so many columns.

# ¿ Sep 3, 2015 21:53

me your dad: Jul 25, 2006

I've looked into a bit but I don't know if Consolidate will to work. It seems like the header data needs to be in the same order for this to work. Other instructions I saw indicate that there can be no blank rows, and I've got a lot of blank rows in my data.

# ¿ Sep 4, 2015 15:09

me your dad: Jul 25, 2006

schmagekie posted:

Here's what I created to sort column headers... I changed it to rely on fewer functions, so hopefully it still works.
To use: create a sheet named "Master" with the correct column headings in row 1.
Run the code.
Each sheet will now have a corresponding sheet with the name "SheetXXX Sorted"

This requires a reference to the Microsoft Scripting Runtime library.
code...

Thanks. I really appreciate it. I've decided to just do it manually. This is going to be a nightmare either way and I've settled into accepting this will be my day. This really falls outside of my normal job too, which adds insult to a poo poo situation.

# ¿ Sep 4, 2015 16:12

me your dad: Jul 25, 2006

I need to populate a column with seemingly random time stamps. I would need to establish a range for the dates - something like, between October 14, 2014 and the current date.

The time stamp would need to be in this format: 9/5/2015 16:08

No time stamp should be the same.

Is this possible?

# ¿ Sep 17, 2015 14:32

me your dad: Jul 25, 2006

fosborb posted:

=RANDBETWEEN(4192600000,NOW()*100000)/100000

There is a nonzero chance of a duplicate. 0.1% in 50,000 generations.

To avoid duplicates, you could generate your list, copy/paste as values, then use Data > Remove Duplicates

Thank you! I entered it and then formatted the cells to date and time and it worked great.

Can you please tell me how this works? For example, what is 4192600000? What are the other numbers?

# ¿ Sep 17, 2015 15:02

me your dad: Jul 25, 2006

A simple question I'm sure:

I had a cell which had both the date and time: 4/19/2017 9:27

I found this page, which uses the INT function to strip out the date.

How does this actually work? In other words, what makes it work?

# ¿ May 22, 2017 15:37

me your dad: Jul 25, 2006

Amazing - thanks for the very clear and comprehensive answer. That explains it very well!

# ¿ May 22, 2017 17:03

me your dad: Jul 25, 2006

I've got a file with about 55,000 rows. Column E has dates in it in this format: 3/24/2018

For each date, there are roughly 8,000 rows of associated data.

The dates aren't consecutive. There are about 8,000 rows associated with 3/24/2018, and then there are roughly 8,000 rows for a 4/26/2018 date, and so on.

Would anyone know a VBA snippet that would split this large workbook into several workbooks (or sheets) based on the dates?

# ¿ Jan 19, 2018 19:55

me your dad: Jul 25, 2006

big crush on Chad OMG posted:

If you have 8k rows needing their own worksheet out of 55k, wouldn�t it just be quicker to add a filter for each date then cut/paste into a new worksheet? Rinse and repeat 6 more times?

I guess so! I never need to use filters so I didn't even think about them. I will definitely do it for this case.

# ¿ Jan 19, 2018 21:12

me your dad: Jul 25, 2006

I'm losing my mind with dates. I have a column displaying dates in mm/dd/yyyy.

I am trying to split them into three columns, using the slash as delimiter.

No matter what I try, the result is three columns that display:

code:

1/1/1900         1/3/1900       7/12/1905

That's what 1/3/2020 gets converted to.

What the hell am I doing wrong? I have the date column formatted as date, along with the columns next to it. When I do text to columns, I am specifying each column during that operation to be formatted date. Nothing seems to work.

I have tried moving the date column to another column in the spreadsheet, I have tried bringing the column into Notepad++ to strip any formatting and pasting it back in.

# ¿ Nov 3, 2020 15:04

me your dad: Jul 25, 2006

Thank you both - I changed the format of the date to general, and it changed it to the numerical string for the date. Then on the adjacent cells I used the Month() Day() and Year() functions to retrieve them. I didn't know about using those.

# ¿ Nov 3, 2020 20:30

me your dad: Jul 25, 2006

Sort of related to this discussion (I think) -

We have an email newsletter at my organization and we're trying to figure out who isn't opening it month after month so they can be taken out of distribution.

What approach would I use if I had five or six columns of email addresses and I wanted to find out who is consistently in each/all of them?

The number of records in each column would likely be in the 20-30k range.

# ¿ Jan 21, 2021 11:43

me your dad: Jul 25, 2006

Thank you both - I haven't yet had a chance to try these solutions. I grossly underestimated the amount of data per file. It's more like 45k to 55k records across six files. I considered today that our email platform provider (Salesforce) may be able to assist us with this from their back end of things. Before I try going forward with the unwieldy process of managing so much data in Excel, I am going to see what they come back with. I opened a case with their support team earlier.

# ¿ Jan 21, 2021 22:59

me your dad: Jul 25, 2006

I'm trying to figure out how to approach a problem, or to see if it is even possible in Excel especially for me, who is a novice.

I have five categories A B C D E

I have 2,000 email addresses which are aligned with at least two of the categories, and a single email address may align with all five categories.

I have been asked to figure out the overlap in the categories. For example, how many people in A/B A/C A/D A/E B/C C/D C/E and so on.

Is this even possible? Aside from the two-category combinations, I need to consider who is in three, four, and five categories. That's a ton of combinations, right?

# ¿ Mar 24, 2022 13:50

me your dad: Jul 25, 2006

Thanks - the good news (I think) for me is I just found out they only need these combos:

A B C

A B D

C D

A B C D

I still have no idea how to do it efficiently.

# ¿ Mar 24, 2022 15:35

me your dad: Jul 25, 2006

kumba posted:

What structure is the data in? Does an email address have a row for each category (e.g., the table structure is Email Address | Category) or is it one row per email with multiple columns (e.g., the table structure is Email Address | Cat A | Cat B etc with a value of 0 or 1 or whatever)

How you have it structured will inform your easiest approach

After a lot of looking at it, I found it very messy. But I was able to clean it up a bit. Each row contains a category and an email address.

Category | Email

timp posted:

Sounds like maybe Conditional Formatting could help you with this? I do something similar at work where I have to compare long columns for duplicates, and this resource is bookmarked on my browser because I can't bother to memorize the formulas https://www.ablebits.com/office-addins-blog/2019/04/30/google-sheets-compare-two-sheets-columns/

Specifically the parts about "Compare two lists and highlight records in both of them" help me a lot

I have indeed utilized conditional formatting and it has been helpful. After some back and forth with the person I am working with, I have reduced the number of records to about 800 and I am working my way through them manually right now.

# ¿ Mar 24, 2022 16:31

Adbot: ADBOT LOVES YOU

# ¿ Apr 27, 2024 23:17

me your dad: Jul 25, 2006

kumba posted:

here's my maybe overengineered shot at it - you can assign unique values to each category such that each individual combination of categories arrives at a distinct sum (think binary operations). so for example

[snip]

there is probably an easier way to do this but this is where my mind went!

Thank you - this is a good approach!

Kibayasu posted:

Are you just counting how many times a combination occurs? That is, for example, 10 emails appear under categories A/B/C. Or do need to have the emails displayed if they match a combination?

I just need the number - 10 in category AB, 125 in category ABD, etc.

# ¿ Mar 24, 2022 17:57

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