- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel - I Need a Wizard
Posted on 3/12/19 at 12:55 pm
Posted on 3/12/19 at 12:55 pm
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.
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/19 at 12:57 pm
3
Posted on 3/12/19 at 1:18 pm to deeprig9
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/19 at 1:19 pm
Posted on 3/12/19 at 1:19 pm to HurricaneDunc
Only the first tab lists the unique asset names.
Posted on 3/12/19 at 1:20 pm to deeprig9
So location is the common field?
Posted on 3/12/19 at 1:22 pm to HurricaneDunc
The only way I see to do it is
filter tab 1,filter tab2,copy,paste, unfilter
636 times
filter tab 1,filter tab2,copy,paste, unfilter
636 times
Posted on 3/12/19 at 1:22 pm to HurricaneDunc
quote:
So location is the common field?
Yes
Posted on 3/12/19 at 1:23 pm to deeprig9
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/19 at 1:24 pm
Posted on 3/12/19 at 1:25 pm to HurricaneDunc
I am not familiar with the vlookup function.
Posted on 3/12/19 at 1:34 pm to deeprig9
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)
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/19 at 1:34 pm
Posted on 3/12/19 at 1:43 pm to deeprig9
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).
Posted on 3/12/19 at 2:00 pm to mdomingue
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?
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/19 at 2:02 pm
Posted on 3/12/19 at 2:04 pm to deeprig9
That's what the dollar signs are for
Posted on 3/12/19 at 2:05 pm to castorinho
Where do I put the dollar sign?
Posted on 3/12/19 at 2:08 pm to deeprig9
Look three posts above 
Posted on 3/12/19 at 2:27 pm to castorinho
Thanks everyone, I can't buy you a beer but I will by myself one and drink it on your behalf.
Posted on 3/13/19 at 10:22 am to deeprig9
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.
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/19 at 10:23 am
Posted on 3/13/19 at 11:03 am to deeprig9
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).
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).
Posted on 3/15/19 at 9:37 pm to deeprig9
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.
should have read further vlookup works too.
This post was edited on 3/15/19 at 9:39 pm
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News