When you click on links to various merchants on this site and make a purchase, this can result in this site earning a commission. Affiliate programs and affiliations include, but are not limited to, the eBay Partner Network.
sheet1 column A has numbers, sheet1 column B has text/desc.
sheet2 column A has numbers, and you want the text from sheet 1 column B in sheet2 column B
=VLOOKUP(A1,Sheet1!A:B,2,FALSE)
EDIT: sorry, misread your question and replied..
glad you were able to figure it out...Indirect is similar to '=' but if you insert/delete rows, EXCEL adjusts '=' but indirect does not get adjusted!
so if you have in sheet1 col A row 1, say the number 3500...in sheet 2, col A row1 put "=indirect(sheet1!a1)" and in col B row1 put "=sheet1!a1"...now go to sheet1 and insert a row above the 3500, excel will adjust the formula in sheet2 col B, but indirect which was fetching the value 3500 will now fetch 0. Try it out!
sheet1 column A has numbers, sheet1 column B has text/desc.
sheet2 column A has numbers, and you want the text from sheet 1 column B in sheet2 column B
=VLOOKUP(A1,Sheet1!A:B,2,FALSE)
EDIT: sorry, misread your question and replied..
glad you were able to figure it out...Indirect is similar to '=' but if you insert/delete rows, EXCEL adjusts '=' but indirect does not get adjusted!
so if you have in sheet1 col A row 1, say the number 3500...in sheet 2, col A row1 put "=indirect(sheet1!a1)" and in col B row1 put "=sheet1!a1"...now go to sheet1 and insert a row above the 3500, excel will adjust the formula in sheet2 col B, but indirect which was fetching the value 3500 will now fetch 0. Try it out!
Good to know about the adjustment with vlookup versus INDIRECT, thanks! Once I had the formula results, I copied and pasted as values since the initial listing of 3500 items will never change.
I tried it with vLookup and eventual got that to work, too. I know so little about the deeper ways to use excel... I had trouble with the vlookup initially because I didn't have column A set to sequential numbers, I just had the actual values (in this case email addresses) so the vlookup was not working. Once I added a column A with 1, 2, 3, etc. it worked like a charm,
In column 1 I have a list of names of people who came into an office. Each name can appear multiple times as they may have come in more than once.
In column 2 I have a second name, of the person the 'column one' name saw when they came into the office . Column one names can be listed with a column two name more than once due to multiple visits.
What I need to do is isolate the column one names that ONLY saw one particular column two name (no matter how many times).
In the following example, I am looking for Mike and Peter (both ONLY ever saw Jeff) but not Mary or Jane:
In column 1 I have a list of names of people who came into an office. Each name can appear multiple times as they may have come in more than once.
In column 2 I have a second name, of the person the 'column one' name saw when they came into the office . Column one names can be listed with a column two name more than once due to multiple visits.
What I need to do is isolate the column one names that ONLY saw one particular column two name (no matter how many times).
In the following example, I am looking for Mike and Peter (both ONLY ever saw Jeff) but not Mary or Jane:
Unless you have a lot of data, wrouldn't a simple Pivot Table work for you? In the table, you'll have go look at which names from col A only have only listing showing from col B but those are the ones you're wanting to find.
I have 7500+ rows I need to sort through. Column A has a date. Column B has a plain text (for sake of simplicity, I am showing it as the name of a color).
There are multiple instances of many dates, some with the same color and some with different colors.
Example:
What I would like to do is compile a listing that shows a single instance of each color present for each specific date.
I.e., I want to end up with:
1/4/18 blue
1/5/18 blue
1/7/18 red
1/9/18 red blue green
...
1/15/18 green red
1/16/18 blue
1/17/18 green
I need to go from multiple daily entries with varying colors to a single entry per day with ALL colors that were previously reported for any entry that day.
let
Source = Excel.CurrentWorkbook(){[Name="TableIn"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ColorList", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"ColorList"}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Re placed Value", {{"ColorList", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ColorList"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ColorList", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Date"}, {{"Colors", each List.Distinct([ColorList]), type text}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Unique Colors", each Text.Combine( [Colors], ", " ), type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Colors"})
in
#"Removed Columns"
What this does--
- read in the data (headers are "Date" and "ColorList")
- set types on the data
- get rid of spaces so we just have comma delimiters
- split columns with more than one color into one row for each color
- group a list of distinct colors by each date
- split that list out, separated by commas and a space for readability
- get rid of the list column
and done.
Put your original data into a table called "TableIn" with column headers as mentioned above. Copy this script into the Advanced Editor under the PowerQuery Editor. Save it and have it load to a new table.
No messing around with constant recalculations or anything. Mikey doesn't believe in cleaning data with the spreadsheet. That's a pre-processing step.
If you are dealing with huge data, this will be much much faster than any array formula based solution as well.
If y'all haven't tried PowerQuery out, you should. Coding is assisted by a GUI, and debugging is a snap because you see the result as soon as you enter the new line in the script.
I've got two rows of data. I need to multiply the row data, then sum the products across columns. I can do it 'brute force' but I'm sure there must be an easier way. What I'm doing:
What this does--
- read in the data (headers are "Date" and "ColorList")
- set types on the data
- get rid of spaces so we just have comma delimiters
- split columns with more than one color into one row for each color
- group a list of distinct colors by each date
- split that list out, separated by commas and a space for readability
- get rid of the list column
and done.
Put your original data into a table called "TableIn" with column headers as mentioned above. Copy this script into the Advanced Editor under the PowerQuery Editor. Save it and have it load to a new table.
No messing around with constant recalculations or anything. Mikey doesn't believe in cleaning data with the spreadsheet. That's a pre-processing step.
If you are dealing with huge data, this will be much much faster than any array formula based solution as well.
OK, so I tried to make this work. Tried.
Ultimately, I needed to get a report out, so I re-generated the output of the data into a slightly different format that I was able to compile what I needed from.
However, I quickly saw the power of PowerQuery, and started going down the rabbit hole of trying to learn how to use it. There are a bunch of "intro's" and such, but I feel a little lost, as most I found assume serious VB skills.
Any good places to start that you recommend to learn how to use PQ?
Ultimately, I needed to get a report out, so I re-generated the output of the data into a slightly different format that I was able to compile what I needed from.
However, I quickly saw the power of PowerQuery, and started going down the rabbit hole of trying to learn how to use it. There are a bunch of "intro's" and such, but I feel a little lost, as most I found assume serious VB skills.
Any good places to start that you recommend to learn how to use PQ?
I am a learn-by-google self-teaching type. I rarely follow a curriculum of any kind when learning a new Excel feature... I usually figure things out as I solve a problem. Expert blogs and developer forums are where I find most of my info
I do have experience with quite a few programming languages though, so when I see a new one I can usually get the gist of it pretty quickly.
I used to like vb, but it is really old tech and clunky compared to the newer tools.
05-27-2015, 01:24 PM
