• Hey, guest user. Hope you're enjoying NeoGAF! Have you considered registering for an account? Come join us and add your take to the daily discourse.

The Excel Help Thread - Pure Excellence

C

ClayKavalier

Banned
I just learned about advanced filters the other day, neat little trick.

I started a new job a few months ago and we exclusively use Excel for data work, I'm not a fan. Programs like R and Stata let you do a lot of things so much more simply.
 
B

BlakeofT

Member
Excel is so useful but also super frustrating. It tries to predict so much I end up spending a lot of time trying to correct it.
 
S

Servbot24

Banned
I have a question GAF! I'm super novice so this might be something that's common knowledge but I had trouble searching it.

I have two separate sheets which both have an ID column, and I want to combine them so that all columns align based on ID. I can't just copy paste since the ID columns for each are slightly different. What would be the best method for this?

Example (actual file is several hundred rows so I can't do it manually):

MFqYuJY.png


EDIT: Surprised this wasn't moved already, lol
 
A

Alcool313

Member
Servbot24 said:
I have a question GAF! I'm super novice so this might be something that's common knowledge but I had trouble searching it.

I have two separate sheets which both have an ID column, and I want to combine them so that all columns align based on ID. I can't just copy paste since the ID columns for each are slightly different. What would be the best method for this?

Example (actual file is several hundred rows so I can't do it manually):

MFqYuJY.png


EDIT: Surprised this wasn't moved already, lol
Click to expand...

Use VLOOKUP. https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
 
F

Funyarinpa

Member
How the fuck do I keep Excel from automatically formatting any date to something like "28 Sept. 2018"? Trawled the menus for a long while but couldn't find the option.
 
N

nampad

Member
Funyarinpa said:
How the fuck do I keep Excel from automatically formatting any date to something like "28 Sept. 2018"? Trawled the menus for a long while but couldn't find the option.
Click to expand...

Home tab, chose the formatting type in the workspace menu (should read long date or so).

or

Right click, format cells, numbers, date - choose what format you want the cell to have.
 
S

snap

Banned
Servbot24 said:
I have a question GAF! I'm super novice so this might be something that's common knowledge but I had trouble searching it.

I have two separate sheets which both have an ID column, and I want to combine them so that all columns align based on ID. I can't just copy paste since the ID columns for each are slightly different. What would be the best method for this?

Example (actual file is several hundred rows so I can't do it manually):

MFqYuJY.png


EDIT: Surprised this wasn't moved already, lol
Click to expand...

sql

joke answer

not really
 
C

cpp_is_king

Member
Alcool313 said:
Use VLOOKUP. https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
Click to expand...

To expand on this, you'll want to use VLOOKUP to create a single merged table, then copy the table and use Paste Values to paste the new table into a new location, then delete the original ones.

Steps:
1. Copy the first ID column into some empty column, say column A.
2. Copy the second ID column into the bottom of the same column.
3. Select all the ID values and choose Data -> Remove Duplicates.

At this point you have a list of all Unique Ids. Let's say this range starts on A1 and goes down. We'll call it "Range"

Now find where you want to put the new table, which will be 3 columns wide. Let's say it's going to start on C3.

4. Now enter this formula into C3, D3, and E3

Code: 
C3 = A1

D3 = VLOOKUP(C3,Table1,2,FALSE)

E3 = VLOOKUP(C3,Table2, 2, FALSE)

5. Drag the formula down until it's the same height as the list of IDs.
6. Highlight this new table and hit Ctrl+C.
7. Find a new place to paste the table and use Paste Special -> Paste Values
8. Delete all the original tables
9. Copy the table you created in step 8 to its final location.
 
T

The Lamp

Member
I'm gonna lurk this thread because I fucking hate doing complicated shit in excel

How can I copy columns of data from multiple excel files and combine and save it into one new excel file?
 
C

cpp_is_king

Member
The Lamp said:
I'm gonna lurk this thread because I fucking hate doing complicated shit in excel

How can I copy columns of data from multiple excel files and combine and save it into one new excel file?
Click to expand...

Does just... copying them not work for some reason? You can open multiple worksheets at once and copy/paste between them.
 
A

Alcool313

Member
The Lamp said:
I want to program something to automate it because I've got hundreds of spreadsheets to do it for
Click to expand...

I know it has been a couple of days, but if you are still interested and the VLOOKUP method above is still too cumbersome, then I would suggest you look into VBA.

Something many do not know is that Excel (and most office products) come with a fairly robust scripting language (Visual Basic for Applications; VBA) and corresponding development environment. Try hitting ALT+F11 when in excel and you can see it for yourself. With this, you can basically automate any task that you can imagine.

It may seem fairly daunting at first, but if you have a knack for problem solving, then it is actually fairly easy to learn and get started. It also helps that there is an extremely robust community for VBA solutions in Excel. If you can think of a problem, chances are that you can copy+paste 90% of the solution after some quick googling.

If this seems something that you would be interested in, feel free to PM me and I will do what I can to get you started (offer open to anyone else interested!).
 
I

InsaneMonkey

Neo Member
So, probably a simple question -

How do I give the Monthly Sales Revenue given what is chosen in the dropdown menus (B6 and B7)?

1LMUfCL.png
 
P

Peltz

Member
great idea for a thread. I'm the only one in my group at work who is good at excel. It's really an essential skill to have.
 
O

oggob

Member
InsaneMonkey said:
So, probably a simple question -

How do I give the Monthly Sales Revenue given what is chosen in the dropdown menus (B6 and B7)?

1LMUfCL.png
Click to expand...

I didn't know you could sum a vlookup like that...

But use the same formula but replace the (2,3,4) part with

Match(B7,A1:A4,0)

this will look for the matching month in the top row and will providing the returning column result... And you don't need the sum function on this one
 
C

ClayKavalier

Banned
InsaneMonkey said:
So, probably a simple question -

How do I give the Monthly Sales Revenue given what is chosen in the dropdown menus (B6 and B7)?

1LMUfCL.png
Click to expand...

Someone else might have other thoughts, I've just started using Excel, but generally you'll have an easier time if you structure your data so that the time variable is in a column instead of a row. Then you can just use a simple SUMIFS formula if the product in the drop down cell matches the product in the product column and the value in the month variable matches the month column.

Hopefully that make sense, typing on my phone or I'd try to give more of an example.


Edit: The post above me will work, but IMO I'd get used to formatting time as a variable if you're learning to work with data. That table you have can easily be replicated with a PivotTable and it'll be easier to make a dashboard with drop-down selections.

PPS: Why is this still in gaming after so long?
 
I

InsaneMonkey

Neo Member
oggob said:
I didn't know you could sum a vlookup like that...
Click to expand...

Yeah me neither, I have no idea what I'm doing honestly, just googling as I go. Thanks for the help.

ClayKavalier said:
Someone else might have other thoughts, I've just started using Excel, but generally you'll have an easier time if you structure your data so that the time variable is in a column instead of a row. Then you can just use a simple SUMIFS formula if the product in the drop down cell matches the product in the product column and the value in the month variable matches the month column.

Edit: The post above me will work, but IMO I'd get used to formatting time as a variable if you're learning to work with data. That table you have can easily be replicated with a PivotTable and it'll be easier to make a dashboard with drop-down selections.
Click to expand...

Thanks for the input, will keep that in mind for next time!
 
C

cpp_is_king

Member
oggob said:
I didn't know you could sum a vlookup like that...

But use the same formula but replace the (2,3,4) part with

Match(B7,A1:A4,0)

this will look for the matching month in the top row and will providing the returning column result... And you don't need the sum function on this one
Click to expand...

Yea that's an array formula. They blew my mind when I first learned about them. For example:

ISRZdOD.png


This is equivalent to using SUMPRODUCT on the two columns.

That said, I really recommend not using them unless you really need to, because it's confusing and there's very often a simpler way to do it.

In this case, I think the best way to do this is using the OFFSET function. Starting with a given cell (or range), the offset will move that range by a specified number of rows and columns. So to sum a particular row, you set your baseline as the header row (Jan - March), use MATCH() to find the index of the chosen fruit, then offset by that number of rows, and pass the result to the SUM function. To sum a particular column, it's exactly the same, but you use the Apple - Coconut as the base range to OFFSET(), then use MATCH again, and offset by the specified number of columns.

Specifically,

=SUM(OFFSET(C1:E1,MATCH(B7,B2:B4,0),0))

will sum the row for the selected fruit, and

=SUM(OFFSET(B2:B4,0,MATCH(B8,C1:E1,0)))

will sum the column for the selected month.

And the best part is, no array formula needed.


ClayKavalier said:
Someone else might have other thoughts, I've just started using Excel, but generally you'll have an easier time if you structure your data so that the time variable is in a column instead of a row. Then you can just use a simple SUMIFS formula if the product in the drop down cell matches the product in the product column and the value in the month variable matches the month column.
Click to expand...

Not sure I follow. What difference does it make if it's in a row or column? Excel can operate on rows just as easily as columns
 
C

ClayKavalier

Banned
cpp_is_king said:
That said, I really recommend not using them unless you really need to, because it's confusing and there's very often a simpler way to do it.
Click to expand...

I love those moments at work when I have to tell someone "The way you figured out how to make this work is genius, but let me show you how to do it with two formulas instead of six."

cpp_is_king said:
Not sure I follow. What difference does it make if it's in a row or column? Excel can operate on rows just as easily as columns
Click to expand...

In this case it doesn't matter much, but in my experience keeping data in a long format is more of a standard for data work than wide. Each column should represent a variable. Software like Stata (and I believe stuff like Python and R? I haven't worked with them as much) assumes that data is in a long format.

Even in Excel it simplifies things, like I said in this case you could solve the problem with a simple SUMIFS formula in stead of messing around with VLOOKUP and OFFSET. And if that table the OP has makes more sense to include in a report or something it can be remade with a PivotTable in a few seconds.

But yeah, it's not necessary, just thought I'd mention that it might be worth keeping in mind as a best practice going forward.
 
C

cpp_is_king

Member
ClayKavalier said:
I
In this case it doesn't matter much, but in my experience keeping data in a long format is more of a standard for data work than wide.
Click to expand...

Ahh, definitely agree long is better than wide. Although in this case there's a maximum of 12 months, and potentially many fruits. But as it's obviously just a toy example, who knows for sure :)
 
C

ClayKavalier

Banned
cpp_is_king said:
Ahh, definitely agree long is better than wide. Although in this case there's a maximum of 12 months, and potentially many fruits. But as it's obviously just a toy example, who knows for sure :)
Click to expand...

Seems odd to assume there's a max of 12 months, I can't think of why they'd only ever need a single year of data. Even if they don't have a full year now it's far better to prepare now.
 
K

Kieli

Member
I'm surprised Excel is as powerful as it is. I've never used it for more than tracking my budget in very basic A3 = A2 - A1 sort of way.
 
G

GodParticle

Member
I've been messing around with excel and wondered how something like the following works.

1LMUfCL.png


Using that fruit question above, if I wanted to total the sales by month(Feb in this case), I could use something like this:

{=SUM(IF(B1:D1="Feb", B2:D4))}

My questions are:
1.Why does it only work if I use an array formula
2).I don't get how the IF statement works. I know if it evaluates true, then do the second part, but I don't know what it's doing with just a range B2:D4
 
C

cpp_is_king

Member
GodParticle said:
I've been messing around with excel and wondered how something like the following works.

1LMUfCL.png


Using that fruit question above, if I wanted to total the sales by month(Feb in this case), I could use something like this:

{=SUM(IF(B1:D1="Feb", B2:D4))}

My questions are:
1.Why does it only work if I use an array formula
2).I don't get how the IF statement works. I know if it evaluates true, then do the second part, but I don't know what it's doing with just a range B2:D4
Click to expand...

This is why I recommend against using them. They're confusing :)

To answer #1, if it werent an array formula, then B1:D1="Feb" would be meaningless. Because it is an array formula though, it basically expands into three different condtiions, B1="Feb", C1="Feb", and D1="Feb", and turning this into three separate IF calls.

The second range, B2:D4 is 3 rows and 3 columns wide, so since the "3 columns" dimension matches up with the 3 columns wide from from the month part, this actually turns into three separate calls

IF(B1="Feb", B2:B4)
IF(C1="Feb", C2:C4)
IF(D1="Feb", D2:D4)

These evaluate to 0, C2:C4, and 0 respectively, so you end up with SUM(0, C2:C4, 0) which is the answer.
 
G

GodParticle

Member
cpp_is_king said:
This is why I recommend against using them. They're confusing :)

To answer #1, if it werent an array formula, then B1:D1="Feb" would be meaningless. Because it is an array formula though, it basically expands into three different condtiions, B1="Feb", C1="Feb", and D1="Feb", and turning this into three separate IF calls.

The second range, B2:D4 is 3 rows and 3 columns wide, so since the "3 columns" dimension matches up with the 3 columns wide from from the month part, this actually turns into three separate calls

IF(B1="Feb", B2:B4)
IF(C1="Feb", C2:C4)
IF(D1="Feb", D2:D4)

These evaluate to 0, C2:C4, and 0 respectively, so you end up with SUM(0, C2:C4, 0) which is the answer.
Click to expand...

Awesome. Thanks for explaining that. You're right, array formulas are kind of confusing >_<
 
You must log in or register to reply here.

Similar threads

What are you reading? |OT|
|OT|
0
329
Cyberpunkd replied
Roboquest is fantastic - FPS Rogue-lite on gamepass
34
2K
nemiroff replied
How would you rank each year of gaming this decade?
18
756
March Climber replied
Weird questions about fictional characters that you've thought about...
Community
16
1K
Heimdall_Xtreme replied
Exposing German Hops
2 3
115
6K
Dr.Morris79 replied
Top Bottom