Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

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

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17404
  • Total likes: 14342
  • DansDeals.com Hat Tips 14
    • 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.
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12863
  • Total likes: 3321
  • 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 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12863
  • Total likes: 3321
  • 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 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17404
  • Total likes: 14342
  • DansDeals.com Hat Tips 14
    • 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!
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12863
  • Total likes: 3321
  • 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 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17404
  • Total likes: 14342
  • DansDeals.com Hat Tips 14
    • 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.
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12863
  • Total likes: 3321
  • 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: 1843
  • Total likes: 52
  • 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 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12863
  • Total likes: 3321
  • 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 Lifetime Platinum Elite
  • *******
  • Join Date: Oct 2013
  • Posts: 1071
  • Total likes: 222
  • DansDeals.com Hat Tips 13
    • View Profile

Offline ual902

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Apr 2013
  • Posts: 1843
  • Total likes: 52
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1110 on: December 01, 2017, 12:51:46 PM »
Select row 7 (by clicking on the "7"), right click on it, click Insert Row. Or select row 7 and type "control shift +"

Thanks that worked.

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 765
  • Total likes: 11
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1111 on: January 01, 2018, 09:40:06 PM »
Is there  way to use text to column and then have all the same data go to same column?
 ex. In a1, mom,dad,son,last name
In b1 son, mom, last name
C1 mom, last name.
D1, mom,son,dad,brother, sister,aunt,last name.

How do I get all the unique values into the same column?

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12863
  • Total likes: 3321
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1112 on: January 01, 2018, 10:36:33 PM »
Is there  way to use text to column and then have all the same data go to same column?
 ex. In a1, mom,dad,son,last name
In b1 son, mom, last name
C1 mom, last name.
D1, mom,son,dad,brother, sister,aunt,last name.

How do I get all the unique values into the same column?
So in the output, you want the "mom" column to be empty if that row didn't have "mom" in it?
Workflowy. You won't know what you're missing until you try it.

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 765
  • Total likes: 11
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1113 on: January 02, 2018, 08:15:34 AM »
So in the output, you want the "mom" column to be empty if that row didn't have "mom" in it?
Yes, but i want the children in their own row.
Would index match work, after text to column?

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12863
  • Total likes: 3321
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1114 on: January 02, 2018, 08:41:27 AM »
Yes, but i want the children in their own row.
Would index match work, after text to column?
I'm thinking you could do this with formulas but I'll need to work it out. Will each column be for a specific child's name, or just any child in general?
Workflowy. You won't know what you're missing until you try it.

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 765
  • Total likes: 11
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1115 on: January 02, 2018, 12:23:55 PM »
I'm thinking you could do this with formulas but I'll need to work it out. Will each column be for a specific child's name, or just any child in general?
Specific child's name.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Help and Problems
« Reply #1116 on: January 02, 2018, 12:32:32 PM »
Specific child's name.
Can you post a sample workbook? Or even screenshots of how you have it before, and how you want it to look after (by doing some manually)?

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12863
  • Total likes: 3321
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1117 on: January 02, 2018, 12:56:25 PM »
Specific child's name.
Perfect!
So keep column A as is, but insert a blank row at the top. In row 1, in columns B, C, etc, put the text that you want to show up in those columns

In B2 put this:
=IF(IFERROR(SEARCH(B$1,$A2),"")="","",B$1)

And copy it to the rest of the workbook.

Sample file attached
Workflowy. You won't know what you're missing until you try it.

Offline stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 6454
  • Total likes: 2751
  • DansDeals.com Hat Tips 269
    • View Profile
Re: Excel Help and Problems
« Reply #1118 on: January 18, 2018, 12:36:02 PM »
Any "fast" ways to clean up a list of names and addresses?

The goal is to get each item in its own column (First, last, street, city, st, zip etcc..) and right now it's a total mess.

Currently the address is one big column which wouldn't be so terrible because I could use "text to columns" but there are also phone numbers (sometimes more then one) randomly thrown in, some before the address and some after and still some have notes like "corner of" so no matter how I cut it there would still be an enormous amount of manual editing.

I have 1540 rows like this  :(

TIA
« Last Edit: January 18, 2018, 12:39:18 PM by stooges44 »
If it's not free shipping it's not worth it.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12863
  • Total likes: 3321
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1119 on: January 18, 2018, 12:39:11 PM »
Any "fast" ways to clean up a list of names and addresses?

The goal is to get each item in its own column (First, last, street, city, st, zip etcc..) and right now it's a total mess.

Currently the address is one big column which wouldn't be so terrible because I could use "text to columns" but there are also phone numbers (sometimes more then one) randomly thrown in, some before the address and some after and still some have notes like "corner of" so now matter how I cut it there would still be an enormous amount of manual editing.

I have 1540 rows like this  :(

TIA
Are you at all familiar with regular expressions? That may be your best bet.
Workflowy. You won't know what you're missing until you try it.