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

Offline chayal101

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Oct 2013
  • Posts: 723
  • Total likes: 92
  • DansDeals.com Hat Tips 2
  • Gender: Female
    • View Profile
Re: Excel Help and Problems
« Reply #1095 on: November 23, 2017, 02:19:59 PM »
I actually want to search all columns that contain specific words, such as Real Estate, Attorneys, Acquisitions, etc... Any "IF" formula I can use?
Chaya L.

Offline Z56

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2014
  • Posts: 2334
  • Total likes: 86
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: NYC, TLV
  • Programs: SPG Gold, Marriot Gold, Hilton Gold, National EE, Hertz GP, Avis PP
Re: Excel Help and Problems
« Reply #1096 on: November 23, 2017, 04:05:23 PM »
I actually want to search all columns that contain specific words, such as Real Estate, Attorneys, Acquisitions, etc... Any "IF" formula I can use?

so use the filter ?

or are looking to create a formula ?
True friends are like diamonds, precious and rare. False friends are like leaves, found everywhere.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2597
  • Total likes: 40
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1097 on: November 23, 2017, 08:38:30 PM »
I actually want to search all columns that contain specific words, such as Real Estate, Attorneys, Acquisitions, etc... Any "IF" formula I can use?

Create a new column that strings together all the columns that might have relevant keywords (=A1&" "&B1&" "&C1). Then create a list of keyword elsewhere. Then select the column with the merged strings and goto Data>Advanced and for the criteria range but in the section with your list of keywords.


Offline aygart

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: May 2008
  • Posts: 7290
  • Total likes: 1856
  • DansDeals.com Hat Tips 7
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1098 on: November 26, 2017, 12:47:57 PM »
I think theis was discussed here previously, but I couldn't find it. I have a list with one column of addresses and multiple utility accounts in the columns to the side of the addresses. I need to change this to have a separate row for each account. Is there any automated way to do this?
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: 9509
  • Total likes: 350
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1099 on: November 26, 2017, 01:07:16 PM »
I think theis was discussed here previously, but I couldn't find it. I have a list with one column of addresses and multiple utility accounts in the columns to the side of the addresses. I need to change this to have a separate row for each account. Is there any automated way to do this?
You can definitely do it with VBA. Question is if there's an easier way.

Two questions:

What's the max number of accounts per address?
Does the sort order matter?
Workflowy. You won't know what you're missing until you try it.

Offline aygart

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: May 2008
  • Posts: 7290
  • Total likes: 1856
  • DansDeals.com Hat Tips 7
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1100 on: November 26, 2017, 01:10:41 PM »
You can definitely do it with VBA. Question is if there's an easier way.

Two questions:

What's the max number of accounts per address?
Does the sort order matter?
They have a max of 3 account columns. A handful of accounts have a forth account in col d of the next row. The list happens to be copied for a Word chart and if it makes it easier to do here that is fine as well. If it means writing VBA it will be easier to just do it manually. I can always sort them over after it is done as long as each account has the address next to it.
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: 9509
  • Total likes: 350
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1101 on: November 26, 2017, 01:12:02 PM »
They have a max of 3 account columns. A handful of accounts have a forth account in col d of the next row. The list happens to be copied for a Word chart and if it makes it easier to do here that is fine as well. If it means writing VBA it will be easier to just do it manually. I can always sort them over after it is done as long as each account has the address next to it.
Great!

Let's say Address is in column A, and the Accounts are in columns B-D.

Go to a blank column (for arguments sake let's use F).
In cell F1, type
Code: [Select]
=A1In G1, type
Code: [Select]
=B1Copy the formulas all the way down.

Then copy columns F-G to a new blank worksheet, being sure to "Paste Values".

Now change G1 to say
Code: [Select]
=C1Copy it all the way down
Select the cells with data, copy them, and Paste Values to your new worksheet, under the existing data.

Now change G1 to say
Code: [Select]
=D1Copy it all the way down
Select the cells with data, copy them, and Paste Values to your new worksheet, under the existing data.

If there are more columns with accounts, repeat as needed.

When you are done, sort by column A.

This may seem like a lot of steps, but it's not really that much once you get the hang of it, and is probably the easiest way to accomplish your goal if it's a one-time thing.
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9509
  • Total likes: 350
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1102 on: November 26, 2017, 01:13:43 PM »
A handful of accounts have a forth account in col d of the next row.
Just noticed this part, we'll have to tweak to handle the fact that there's accounts in the next row.

In the next row, is address filled in, or is it blank?
Workflowy. You won't know what you're missing until you try it.

Offline aygart

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: May 2008
  • Posts: 7290
  • Total likes: 1856
  • DansDeals.com Hat Tips 7
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1103 on: November 26, 2017, 01:16:34 PM »
Great!

Let's say Address is in column A, and the Accounts are in columns B-D.

Go to a blank column (for arguments sake let's use F).
In cell F1, type
Code: [Select]
=A1In G1, type
Code: [Select]
=B1Copy the formulas all the way down.

Then copy columns F-G to a new blank worksheet, being sure to "Paste Values".

Now change G1 to say
Code: [Select]
=C1Copy it all the way down
Select the cells with data, copy them, and Paste Values to your new worksheet, under the existing data.

Now change G1 to say
Code: [Select]
=D1Copy it all the way down
Select the cells with data, copy them, and Paste Values to your new worksheet, under the existing data.

If there are more columns with accounts, repeat as needed.

When you are done, sort by column A.

This may seem like a lot of steps, but it's not really that much once you get the hang of it, and is probably the easiest way to accomplish your goal if it's a one-time thing.
This is a perfect way and beautifully simple.
Just noticed this part, we'll have to tweak to handle the fact that there's accounts in the next row.

In the next row, is address filled in, or is it blank?
There are only 3 of those so I just copied their address down for them too.

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: 9509
  • Total likes: 350
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1104 on: November 26, 2017, 01:17:31 PM »
This is a perfect way and beautifully simple.There are only 3 of those so I just copied their address down for them too.

Thanks!
Great - glad to help!
Workflowy. You won't know what you're missing until you try it.

Offline aygart

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: May 2008
  • Posts: 7290
  • Total likes: 1856
  • DansDeals.com Hat Tips 7
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1105 on: November 26, 2017, 01:23:30 PM »
Great - glad to help!
It worked great! Since some had fewer accounts i needed to also filter the zeros and delete those rows.
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: 9509
  • Total likes: 350
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1106 on: November 26, 2017, 01:24:22 PM »
It worked great! Since some had fewer accounts i needed to also filter the zeros and delete those rows.
Right that makes sense
Workflowy. You won't know what you're missing until you try it.

Offline ual902

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Apr 2013
  • Posts: 1624
  • Total likes: 15
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1107 on: November 30, 2017, 02:47:34 AM »
How do I insert into the next row or line, I need to add more expnses?


So it should look like this:



This was exported from Quickbooks into Excel

Thanks-
« Last Edit: November 30, 2017, 02:55:22 AM by ual902 »

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9509
  • Total likes: 350
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1108 on: November 30, 2017, 07:53:14 AM »
How do I insert into the next row or line, I need to add more expnses?


So it should look like this:



This was exported from Quickbooks into Excel

Thanks-
Select row 7 (by clicking on the "7"), right click on it, click Insert Row. Or select row 7 and type "control shift +"
Workflowy. You won't know what you're missing until you try it.

Offline Toasted

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Oct 2013
  • Posts: 963
  • Total likes: 78
  • DansDeals.com Hat Tips 7
    • View Profile