+ Reply to Thread
Results 1 to 8 of 8

Move a row of data based on a word

  1. #1
    Registered User
    Join Date
    02-10-2022
    Location
    Worthing, United Kingdom
    MS-Off Ver
    365
    Posts
    3

    Question Move a row of data based on a word

    Hello,

    I am new to the forum and I would be grateful for any kind of help as this is driving me crazy!!

    I have attached my blank document for reference.

    My mum currently buys, restores, and resells furniture for a profit and at the moment she doesn't have anything electronic so I thought excel would be great.

    I have 3 tabs. The first one is 'Current Stock' which will outline what stock she has and then on the side I have made a formula to calculate how much the stock is in the value of money.

    The second one is 'Sold Stock'. This is where sold items will go. I have made another formula to the side to add up all of the profits made and also minus off expenses which is tab 3.


    I have been trying for ages to find a code to allow the data for an item to move from 'Current Stock' to 'Sold Stock' when the word 'Sold' is input into the 'Confirm' column. Can anyone help as I have tried many VBA codes and nothing has worked!!
    I am more than happy to answer any questions for clarification
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Move a row of data based on a word

    Personally, I think the Sold Stock sheet is redundant. You might as well leave it where it is. It's either sold or it's not, or maybe it could be reserved. I would allocate a stock code/ID for each piece of furniture and use that to record expenses. Then you can use SUMIF/SUMIFS to calculate the total cost of refurbishment for an item.

    You can use AutoFilter to show which items are still in stock, or those that have been sold.

    If you don't know VBA, I’d suggest you avoid it.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    02-10-2022
    Location
    Worthing, United Kingdom
    MS-Off Ver
    365
    Posts
    3

    Re: Move a row of data based on a word

    Thank you for your response.
    I'm not to sure how to use SumIf but i will certainly look into it!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Move a row of data based on a word

    Please see the updated sample workbook. I have used two sheets: Current Stock and Expenses. Each contains one Structured Table. If you type a new entry immediately below the table, the table will be extended to include it. Formulae and formatting will automatically be copied into the new row.

    Structured Tables allow you to create "more readable" formula as they reference the column headings. For example:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I have added a unique ID so that you can differentiate between the different furniture items ... as time goes on, you will have more than one desk, chair or mirror. This allows you to link specific expenses with specific furniture items. I guess you might want to apportion the cost of bulk items like sandpaper, paint, varnish, screws, etc.

    I have also adjusted the formatting. I think you are mixing up presentation with content ... in my opinion, less is more. It will make it easier should you wish to print the sheets.

    You'll see that the two sheets are linked together. The expenses for an item are a total of the relevant amounts on the Expenses sheet.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    And the Description on the Expenses sheet uses a VLOOKUP:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Anyway, some ideas to be going on with. I'm sure it will evolve over time.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-11-2022
    Location
    Spokane, Washington USA
    MS-Off Ver
    365
    Posts
    3

    Re: Move a row of data based on a word

    Nicely done.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Move a row of data based on a word

    Nicely done.
    Why, thank you

  7. #7
    Registered User
    Join Date
    02-10-2022
    Location
    Worthing, United Kingdom
    MS-Off Ver
    365
    Posts
    3

    Re: Move a row of data based on a word

    I can't thank you enough, this is fantastic!!

    Thank you so much. It's really neat and works perfectly. I love the table automatically generating

    Many thanks,
    Luke!

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Move a row of data based on a word

    You're welcome.


    Note that you can Sort and/or Filter items in a Structured Table. That would allow you to compare similar items.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] move amounts from column to another based on word in another column
    By Hasson in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-04-2021, 06:36 AM
  2. Move Word Data to Excel
    By Kyhosa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-21-2018, 11:59 AM
  3. [SOLVED] move data from diff rows in 1 column into new columns based on dup data in another field
    By lxndeb in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-01-2014, 11:50 PM
  4. macro if data in column a = set word to insert that column into sheet 2 or 3 based on word
    By ExcelBeginner326 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-13-2014, 01:57 PM
  5. Want to move Excel data to a Word Form
    By brandomcg in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-22-2013, 07:01 AM
  6. Macro to move word document based on email address
    By kingsley in forum Excel General
    Replies: 0
    Last Post: 10-03-2011, 02:25 PM
  7. Macro to move data from Excel to Word
    By Wendy_The_Intern_<3 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-19-2011, 03:41 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1