Posted by
Message
deeprig9
Georgia Fan
Member since Sep 2012
33647 posts

Excel - I Need a Wizard
I had great success the last time I came here for some Excel advice. I hope the wizards can help me again.


I have a worksheet with two tabs.

Tab 1 Column A lists an asset name.
Tab 1 Column B lists the location of the asset.
Tab 1 Column C,D,E are blank, needs input (from Tab 2).

Tab 2 Column A lists an asset location (without the asset name).
Tab 2 Column B lists an attribute to that location.
Tab 2 Column C lists another attribute to that location.
Tab 2 column D lists a third attribute to that location.

How do I get the data in Tab 2 B,C,D into Tab 1 C,D,E ?

It's about 6000 assets across 636 different locations, which is why I don't want to do this manually.


This post was edited on 3/12 at 12:57 pm


HurricaneDunc
Bowling Green Fan
Houston
Member since Nov 2008
10318 posts

re: Excel - I Need a Wizard
Does each tab have a line that uniquely identifies the asset? i.e. a common field If so, vlookup.
This post was edited on 3/12 at 1:19 pm


deeprig9
Georgia Fan
Member since Sep 2012
33647 posts

re: Excel - I Need a Wizard
Only the first tab lists the unique asset names.


HurricaneDunc
Bowling Green Fan
Houston
Member since Nov 2008
10318 posts

re: Excel - I Need a Wizard
So location is the common field?


deeprig9
Georgia Fan
Member since Sep 2012
33647 posts

re: Excel - I Need a Wizard
The only way I see to do it is

filter tab 1,filter tab2,copy,paste, unfilter

636 times


deeprig9
Georgia Fan
Member since Sep 2012
33647 posts

re: Excel - I Need a Wizard
quote:

So location is the common field?


Yes


Replies (0)
Replies (0)
00
HurricaneDunc
Bowling Green Fan
Houston
Member since Nov 2008
10318 posts

re: Excel - I Need a Wizard
Vlookup will work using the location field. Using Tab 1 column B and tab 2 column A as the reference. Are you familiar with the vlookup function?
This post was edited on 3/12 at 1:24 pm


deeprig9
Georgia Fan
Member since Sep 2012
33647 posts

re: Excel - I Need a Wizard
I am not familiar with the vlookup function.


Replies (0)
Replies (0)
00
castorinho
Oklahoma Fan
13623 posts
Member since Nov 2010
57696 posts

re: Excel - I Need a Wizard
The way you've explained this, sheet 2 column A should all be different (locations). If that's the case, then.

C2=Vlookup(B2, Sheet2!$A$2:$B$637,2,False)
D2=Vlookup(B2,Sheet2!$A$2:$C$637,3,False)
This post was edited on 3/12 at 1:34 pm


Replies (0)
Replies (0)
30
mdomingue
LSU Fan
Lafayette, LA
Member since Nov 2010
8819 posts

re: Excel - I Need a Wizard
quote:

Tab 2 Column B lists an attribute to that location.
Tab 2 Column C lists another attribute to that location.
Tab 2 column D lists a third attribute to that location.


Define attributes vs assets for the purposes of this.

quote:

It's about 6000 assets across 636 different locations, which is why I don't want to do this manually.


I'm asking if the assets you discuss here are the asset name in Tab A or the Attributes in Tab B. In other words is there 1 attribute name per attribute location or multiple.

Vlookup sounds like a good option. it may be even easier if the number of rows in Tab A and Tab B are equal (one row per location).


deeprig9
Georgia Fan
Member since Sep 2012
33647 posts

re: Excel - I Need a Wizard
Vlookup is working for me, yall gave me enough to google and try it out.

The problem I'm having now is cutting and pasting the formula, it is not just changing my "ID" cell from line to line, it is changing the search range line by line too.


Line 1 Formula=

=VLOOKUP(B1,'Sheet Name 2'!A1:B636,2,FALSE)

Copy and past to Line 2,

=VLOOKUP(B2,'Sheet Name 2'!A2:B637,2,FALSE)


Obvioulsy pasting the formula down 6000 lines, this isn't going to work.

Any trick to pasting formulas that will maintain the search range but still update the B1 part?
This post was edited on 3/12 at 2:02 pm


castorinho
Oklahoma Fan
13623 posts
Member since Nov 2010
57696 posts

re: Excel - I Need a Wizard
That's what the dollar signs are for


deeprig9
Georgia Fan
Member since Sep 2012
33647 posts

re: Excel - I Need a Wizard
Where do I put the dollar sign?


castorinho
Oklahoma Fan
13623 posts
Member since Nov 2010
57696 posts

re: Excel - I Need a Wizard
Look three posts above


deeprig9
Georgia Fan
Member since Sep 2012
33647 posts

re: Excel - I Need a Wizard
Thanks everyone, I can't buy you a beer but I will by myself one and drink it on your behalf.



fibonaccisquared
Georgia Fan
The mystical waters of the Hooch
Member since Dec 2011
13047 posts

re: Excel - I Need a Wizard
For future reference, the $ is how you lock an aspect of a formula. If you want the column to remain locked, put it in front of the alpha value. If you want the row to remain locked, put it in front of the numeric value. Or for both, do both.

Example:

$A1 - This would lock the column in regardless of where you place it in the sheet, but the row will be relative to the new location (ie. if it was moved to the 5th row it will become $A5

A$1 - Opposite. Locks the row, but if pasted in column E, becomes E$1

$A$1 - Locks both, doesn't matter where you paste it in on the page.


I usually won't worry with the dollar signs until I start figuring out what the overall layout is going to look like on a lot of my projects, but for things like V-lookup, it's usually pretty clear what your ranges/locations of cells will be.

Good luck.
This post was edited on 3/13 at 10:23 am


Replies (0)
Replies (0)
50
WhiskeyThrottle
Member since Nov 2017
672 posts

re: Excel - I Need a Wizard
Shortcut for the $ is F4.

If you used the formula box to enter the VLookup, you would claim the table array, and then hit F4 and it will freeze the lookup table criteria.

If you're typing the formula out in the formula bar, once you claim your table array hit F4 and it will add the $ where they need to go.

(Lookup_value,table array(hit F4 here),Col_index_num, Range_lookup).


Replies (0)
Replies (0)
40
oklahogjr
Arizona State Fan
Gold Membership
Member since Jan 2010
27795 posts

re: Excel - I Need a Wizard
turn both into tables and then use a power query to do a full join to produce the dataset. you'll select location as the column in the menu option.

should have read further vlookup works too.
This post was edited on 3/15 at 9:39 pm


Replies (0)
Replies (0)
00
first pageprev pagePage 1 of 1next pagelast page
refresh

Back to top

logoFollow TigerDroppings for LSU Football News
Follow us on Twitter, Facebook and Instagram to get the latest updates on LSU Football and Recruiting.

FacebookTwitterInstagram