Any Microsoft Excel Experts?
#41
Needs more Lemon Pledge
NVM, got it using =INDIRECT
#42
takin care of Business in
iTrader: (5)
Join Date: Jan 2008
Location: Kansas City, MO
Age: 40
Posts: 30,994
Received 4,732 Likes
on
4,064 Posts
here is how I imagine:
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!
Last edited by swoosh; 05-27-2015 at 01:48 PM.
The following users liked this post:
stogie1020 (05-27-2015)
#43
Needs more Lemon Pledge
here is how I imagine:
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,
Thanks!
#44
Team Owner
Stoges you could also use =INDEX(). Cool thing about that is your array could be two dimensional.
The following users liked this post:
stogie1020 (05-27-2015)
#45
Needs more Lemon Pledge
I will have to check that out, thanks!
EDIT: Mike, Index would have been perfect before I added the sequential numbers column to the list for the vlookup.
Dammit, there are three ways to do what I wanted!
EDIT: Mike, Index would have been perfect before I added the sequential numbers column to the list for the vlookup.
Dammit, there are three ways to do what I wanted!
Last edited by stogie1020; 05-27-2015 at 02:13 PM.
#46
takin care of Business in
iTrader: (5)
Join Date: Jan 2008
Location: Kansas City, MO
Age: 40
Posts: 30,994
Received 4,732 Likes
on
4,064 Posts
^^^ +1
you can use INDEX along with MATCH...
MATCH returns the column/row number of the string it matches. You can feed that into INPUT to get the contents of the cell.
I think vlookup is your best option unless you want to manipulate cells
you can use INDEX along with MATCH...
MATCH returns the column/row number of the string it matches. You can feed that into INPUT to get the contents of the cell.
I think vlookup is your best option unless you want to manipulate cells
The following users liked this post:
stogie1020 (05-27-2015)
#47
Needs more Lemon Pledge
OK, slightly more complex question now...
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:
#48
Racer
OK, slightly more complex question now...
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:
#49
Needs more Lemon Pledge
I come to the well of excel knowledge once again.
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
Any ideas how to do this?
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
Any ideas how to do this?
#50
Needs more Lemon Pledge
Forget my last post. I oversimplified it too much. Working on a better example of the actual data.
#51
Needs more Lemon Pledge
OK, here is what I really need:
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.
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.
#52
Senior Moderator
VLOOKUP and CONCAT/TEXTJOIN will be your friend in this case:
https://www.get-digital-help.com/exc...into-one-cell/
somewhat similar use case
https://www.get-digital-help.com/exc...into-one-cell/
somewhat similar use case
#53
Needs more Lemon Pledge
VLOOKUP and CONCAT/TEXTJOIN will be your friend in this case:
https://www.get-digital-help.com/exc...into-one-cell/
somewhat similar use case
https://www.get-digital-help.com/exc...into-one-cell/
somewhat similar use case
#54
Team Owner
I'd use PowerQuery to unpivot the colors and then regroup the dates with a list of colors.
But I'm weird.
But I'm weird.
#55
Team Owner
Here's the PowerQuery script:
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.
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"
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"
- 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.
Last edited by svtmike; 12-17-2019 at 09:11 PM.
#56
Needs more Lemon Pledge
Wow, that looks awesome Mike. I will play with this today and see if I can get it to work.
Thanks!
Thanks!
#57
Senior Moderator
No VBA solution?
#58
Team Owner
#59
Team Owner
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.
#60
AZ Community Team
Join Date: May 2007
Location: N35°03'16.75", W 080°51'0.9"
Posts: 32,488
Received 7,771 Likes
on
4,342 Posts
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:
(a1*a2)+(b1*b2)+(c1*c2)+(d1*d2)+ ..... +(x1*x2)
(a1*a2)+(b1*b2)+(c1*c2)+(d1*d2)+ ..... +(x1*x2)
#61
Team Owner
=sumproduct( a1:x1, a2:x2 )
The following users liked this post:
Bearcat94 (12-21-2019)
#62
Needs more Lemon Pledge
Here's the PowerQuery script:
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.
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.
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?
#63
Team Owner
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 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.
Thread
Thread Starter
Forum
Replies
Last Post
aunggu2002
2G CL (2001-2003)
72
01-06-2008 05:29 PM