Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 57751 times)

Offline Alexsei

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2011
  • Posts: 2635
  • Total likes: 57
  • DansDeals.com Hat Tips 3
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: Excel Help and Problems
« Reply #1140 on: February 21, 2018, 06:41:05 PM »
i have two sheets both have a unique identifier in column A containing the order number, what i want is to fill info in blank column D of sheet1 based on what's in column D in sheet2 for the same order number
When life gives you peanuts, make peanut chew

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8991
  • Total likes: 216
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1141 on: February 21, 2018, 06:45:28 PM »
i have two sheets both have a unique identifier in column A containing the order number, what i want is to fill info in blank column D of sheet1 based on what's in column D in sheet2 for the same order number
VLOOKUP

Example:

=VLOOKUP(A2,Sheet2!A:D,4,0)

A2 is the order # in the current sheet, Sheet2!A:D is where to look in the other sheet, 4 means return the result from the 4th column (since the range starts at column A, the 4th column is D). 0 means only return results if the order number matches exactly.
Workflowy. You won't know what you're missing until you try it.

Offline Alexsei

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2011
  • Posts: 2635
  • Total likes: 57
  • DansDeals.com Hat Tips 3
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: Excel Help and Problems
« Reply #1142 on: February 22, 2018, 10:25:38 AM »
VLOOKUP

Example:

=VLOOKUP(A2,Sheet2!A:D,4,0)

A2 is the order # in the current sheet, Sheet2!A:D is where to look in the other sheet, 4 means return the result from the 4th column (since the range starts at column A, the 4th column is D). 0 means only return results if the order number matches exactly.
Thanks!
When life gives you peanuts, make peanut chew

Offline MasterAmex

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2015
  • Posts: 283
  • Total likes: 4
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1143 on: February 22, 2018, 02:32:56 PM »
VLOOKUP

Example:

=VLOOKUP(A2,Sheet2!A:D,4,0)

A2 is the order # in the current sheet, Sheet2!A:D is where to look in the other sheet, 4 means return the result from the 4th column (since the range starts at column A, the 4th column is D). 0 means only return results if the order number matches exactly.
possible to use VLOOKUP to return all data matching the value? (i am not using a UI)

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8991
  • Total likes: 216
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1144 on: February 22, 2018, 02:34:52 PM »
possible to use VLOOKUP to return all data matching the value? (i am not using a UI)
Do you mean all the columns for that row, or multiple rows that have the same value?
Workflowy. You won't know what you're missing until you try it.

Offline MasterAmex

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2015
  • Posts: 283
  • Total likes: 4
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1145 on: February 22, 2018, 02:45:50 PM »
Do you mean all the columns for that row, or multiple rows that have the same value?
multiple rows

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8991
  • Total likes: 216
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1146 on: February 22, 2018, 02:47:33 PM »
multiple rows
I don't think that can be done with vlookup, there's probably a way using array formulas but I'm not so familiar with them. Hopefully someone else  can answer!
Workflowy. You won't know what you're missing until you try it.

Online churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6843
  • Total likes: 153
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1147 on: February 22, 2018, 04:24:03 PM »
Hlookup?

Offline MasterAmex

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2015
  • Posts: 283
  • Total likes: 4
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1148 on: February 22, 2018, 04:54:28 PM »
Hlookup?
what is that, and how should i use it?

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2570
  • Total likes: 73
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1149 on: February 22, 2018, 05:04:54 PM »
what is that, and how should i use it?
HLOOKUP and VLOOKUP are the same, except one works horizontally and one works vertically.

What are you trying to do? If you want say a sum of the values from a particular column in the source table that match the identifier you select, use SUMIF. If you want to start with the identifier and return separate cells for each instance of that identifier in the source table, you need an array formula. Start here: https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/

Offline Entrepreneur

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2011
  • Posts: 471
  • Total likes: 1
  • DansDeals.com Hat Tips 4
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1150 on: March 08, 2018, 11:40:37 PM »
How do I make a scatter plot graph where the X and Y axis have negative numbers (graph should look like a plus sign) and where I want my plots to be different sizes based on a 3rd column of data?

Attached is the data where the actual plots are the Code field numbers, X and Y axis data are the x and y axis fields,  and the size of the plots should be based on the Plot Size field.

Thanks!

Online aygart

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: May 2008
  • Posts: 5938
  • Total likes: 892
  • DansDeals.com Hat Tips 7
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1151 on: March 12, 2018, 01:42:51 PM »
I have the date (mm/dd/yyyy) and the hour number only (hh) in 2 separate columns with a third column of data for each hour. I need to make a chart showing the data but readable to see at least some of the days to be able to see on the chart which data is from when. How can I do that? I was thinking to make a new column of MM/DD/YYYY HH:MM:SS but cannot get the 2 columns to combine into that.
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8991
  • Total likes: 216
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1152 on: March 12, 2018, 02:56:13 PM »
I have the date (mm/dd/yyyy) and the hour number only (hh) in 2 separate columns with a third column of data for each hour. I need to make a chart showing the data but readable to see at least some of the days to be able to see on the chart which data is from when. How can I do that? I was thinking to make a new column of MM/DD/YYYY HH:MM:SS but cannot get the 2 columns to combine into that.
Does something like this work?

Assuming that column A is the date and column B is the hour.
Code: [Select]
=TEXT(A2,"m/dd/yy ")&TEXT(TIME(B2,0,0),"h:mm AM/PM")
Workflowy. You won't know what you're missing until you try it.

Online aygart

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: May 2008
  • Posts: 5938
  • Total likes: 892
  • DansDeals.com Hat Tips 7
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1153 on: March 12, 2018, 03:21:10 PM »
Does something like this work?

Assuming that column A is the date and column B is the hour.
Code: [Select]
=TEXT(A2,"m/dd/yy ")&TEXT(TIME(B2,0,0),"h:mm AM/PM")
Worked great. Thanks!
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8991
  • Total likes: 216
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1154 on: March 12, 2018, 03:35:05 PM »
Worked great. Thanks!
Awesome, you're welcome!
Workflowy. You won't know what you're missing until you try it.