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 79087 times)

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5047
  • Total likes: 10
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Problem
« Reply #180 on: July 13, 2014, 01:15:07 AM »
Highlight based on text and sort by highlight? Not sure I understand the case exactly

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4394
  • Total likes: 1
  • DansDeals.com Hat Tips 8
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #181 on: July 13, 2014, 01:39:32 AM »
I simplified the scenario. I have  list of hundreds of transactions. I also have about 10 columns of the different people involved in that transaction. I need to see all the transactions Bob was involved with. He might be in the first column on one transaction, the third on another and the 10th column on another. It would be time consuming to do it separately.
Concatenate all columns containing the names and filter by that column

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4360
  • Total likes: 191
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Excel Problem
« Reply #182 on: July 13, 2014, 03:35:08 AM »
If I have spreadsheet with 2 columns. Each colum containing a list of names. Lest say column A has Bob, Joe, and Steve in that order. And column B has Joe Steve and  Bob int hat order.
How would I filter out all the records that have Bob at once.
If I filter column A for Bob I right away lose the next two rows so I can't further filter column B for Bob too.
Basically is there a way to have multiple column filters work as an AND, meaning together with the previous filters.
Seems like a simple IF clause should do it, if you're looking for anything that's the same, not just Bob.
Try '=IF(A2=B2,"same","different") then you can filter anything that's "same".

If you want only ones where Bob is the same, then Smurfs suggestion should work.
« Last Edit: July 13, 2014, 03:38:09 AM by lubaby »
Once the game is over, the king and the pawn go back in the same box.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7105
  • Total likes: 252
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #183 on: July 13, 2014, 07:30:42 AM »
You can also sort by multiple columns.
Not sure if this would work for you.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9227
  • Total likes: 279
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #184 on: July 13, 2014, 01:54:00 PM »
Workflowy. You won't know what you're missing until you try it.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2564
  • Total likes: 10
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #185 on: July 13, 2014, 01:54:11 PM »
If I have spreadsheet with 2 columns. Each colum containing a list of names. Lest say column A has Bob, Joe, and Steve in that order. And column B has Joe Steve and  Bob int hat order.
How would I filter out all the records that have Bob at once.
If I filter column A for Bob I right away lose the next two rows so I can't further filter column B for Bob too.
Basically is there a way to have multiple column filters work as an AND, meaning together with the previous filters.

You're looking for "advanced filter", it's on the data tab to the right of the filter button. You can filter multiple columns using AND or OR.
http://office.microsoft.com/en-us/excel-help/filter-by-using-advanced-criteria-HP005200178.aspx

The video here gives a good overview, but doesn't have your specific case. You will need to put Bob in A3, B4, C5 ect. The reason is because criteria in the same row are AND, but you want OR, so you need to put then in different rows in the columns you want to filter.

Yip: If you need to keep on changing the names, you can find a blank call and use that to write the name in, and write in A3, B4, C5 etc =that blank cell. (eg. write Bob in K1, and write in A3, B4, C5 etc =K1, now when you want to switch names just change the name in K1. You will have to clear and re-apply the filter, unless you write a macro to do it automatically.)

(Advanced filters do not work in shared workbooks unless you program it in VBA).

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7105
  • Total likes: 252
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #186 on: July 16, 2014, 10:04:32 AM »
Need a formula to insert random dates within a range in a column.

(preparing a second quarter report of various activities, need to fill in the date column...) 

ETA found this: http://www.techrepublic.com/blog/microsoft-office/generate-random-dates-within-a-specific-date-range/

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4360
  • Total likes: 191
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Excel Problem
« Reply #187 on: July 16, 2014, 10:05:41 AM »
Need a formula to insert random dates within a range in a column.

(preparing a second quarter report of various activities, need to fill in the date column...) 
http://www.techrepublic.com/blog/microsoft-office/generate-random-dates-within-a-specific-date-range/

Quote
With just a little more work, you can use RANDBETWEEN() to generate random dates for a specific time period. You'll include the DATE() function to specify the bottom and top values as dates, using the form

RANDBETWEEN(DATE(bottomdate),DATE(topdate)
For example, to return random dates between January 1, 2012 and January 30, 2012, you'd use the following function:

=RANDBETWEEN(DATE(2012,1,1),DATE(2012,1,30))
Once the game is over, the king and the pawn go back in the same box.

Offline MC

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2013
  • Posts: 1742
  • Total likes: 39
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: NY
Excel Problem
« Reply #188 on: July 16, 2014, 10:07:45 AM »
Need a formula to insert random dates within a range in a column.

(preparing a second quarter report of various activities, need to fill in the date column...)
I think this works: RANDBETWEEN(DATE(bottomdate),DATE(topdate))
Let me know if it doesn't

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7105
  • Total likes: 252
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7105
  • Total likes: 252
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #190 on: July 16, 2014, 10:14:10 AM »
But how do I get it to stop auto calculating everytime I enter data in a row? I guess I can change them to values...

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2564
  • Total likes: 10
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #191 on: July 16, 2014, 10:40:41 AM »
But how do I get it to stop auto calculating everytime I enter data in a row? I guess I can change them to values...

Gotta change to values...

Offline Gets

  • Dansdeals Gold Elite
  • ***
  • Join Date: Jan 2013
  • Posts: 218
  • Total likes: 0
  • DansDeals.com Hat Tips 1
    • View Profile
Excel Problem
« Reply #192 on: July 16, 2014, 09:03:50 PM »
Hi,
I ran a report at work and it divided the rows up into 3 sets of 600 rows instead of 1 wide row of 600. Is there any way to put them side by side matching the user Id of each row.
Please let me know if I can be any clearer. Tia

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5047
  • Total likes: 10
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Problem
« Reply #193 on: July 16, 2014, 09:09:46 PM »
Meaning it split the data into columns a, b and c rows 1 - 600? or column a rows 1 - 1800?

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1304
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Problem
« Reply #194 on: July 16, 2014, 09:26:09 PM »
ever tried v-lookup?
you can only make a first impression ONCE