Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Autofill macro or anything that could help.....


  • Please log in to reply
2 replies to this topic

#1 Ray911

Ray911

  • Members
  • 30 posts
  • OFFLINE
  •  
  • Local time:09:23 AM

Posted 05 September 2010 - 03:21 PM

Hello,

I have a XLSX file of my products list, there are 3959 parts total. They go like this

Column A
07 11 9 900 250
07 11 9 900 673
07 11 9 901 294
07 11 9 901 438.. so on

What i want to do is leave the existing as they are however add 3 more rows for each product like so..

07 11 9 900 250
07 11 9 900 250-E
07 11 9 900 250-G
07 11 9 900 250-T
07 11 9 900 673
07 11 9 900 673-E
07 11 9 900 673-G
07 11 9 900 673-T
07 11 9 901 294
07 11 9 901 294-E
07 11 9 901 294-G
07 11 9 901 294-T.. so o

TO import them into Peachtree,
I tried autofill, autofill with ctrl and shift buttons by editing the first five as shown in second list and highlighting them till the sixth and then pulling autofill.
However i am not able to use autofill as I m not familiar with macros or anything advanced in excel.

So tell me if theres a formula or a autofill macro or anything that can solve this.

So the main goal is to add a letters after every part number thrice (E, G & T)

Really appreciate any help,
Please post if u need the files .

FYI: I m using Excel 2007 and have to make this edit in xlsx file and later copy it into a CSV file to be imported into Peachtree.

Configuration: Windows XP (SP3).
Screw Malware & All Kinds of viruses using OUR BELOVED HJT

BC AdBot (Login to Remove)

 


#2 Budapest

Budapest

    Bleepin' Cynic


  • Moderator
  • 23,573 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:04:23 PM

Posted 07 September 2010 - 03:55 PM

You could set up a second column with the (E, G & T) data you want to add and then merge the cells using the CONCATENATE command.
The power of accurate observation is commonly called cynicism by those who haven't got it.

—George Bernard Shaw

#3 adupuis

adupuis

  • Members
  • 5 posts
  • OFFLINE
  •  
  • Local time:02:23 AM

Posted 07 September 2010 - 05:21 PM

Hi Ray911,
Actually, I believe Budapest is on the right track. I might be able to fill in some of the blanks.
Step 1 - in column B - enter the following: =A1 & "-E"
Step 2 - in column C - enter the following: =A1 & "-G"
Step 3 - in column D - enter the following: =A1 & "-T"
Step 4 - use your mouse to select B1 to D1, then press CTRL+C (to copy these three cells)
Step 5 - click on cell A1, and then CTRL+Down to go to the bottom of your data
Step 6 - click on the cell directly to the right of the last cell in your list only once to select it(possibly cell B3959)
Step 7 - press SHIFT+CTRL+Up on your keyboard (which should select all of column B with matching data in column A)
Step 8 - press CTRL+V to paste the formulas from Steps 1 to 3; this should give you four columns as follows: A = your original list, B = your original list with -E appended, C = your original list with -G appended, your original list with -T appended
Step 9 - select all of columns B through D, press CNTRL+C then right click on cell B1, and select Paste Special then Values from the resulting menus.
Step 10 - select all of the list in column B, press CNTRL+C, go to the bottom of your list in column A, and press CNTRL+V, to paste the new values under your original list.
Step 11 - Do the same with coumns C and E, copying each after the last cell in list of column A.
Step 12 - you can now delete columns B, C, and D, and sort column A to get your complete list.

If this doesn't meet your needs, you may have to go the macro way. I can help you with that, as I believe this would be a pretty simple recursive macro to put together.

Let me know how this works out for you.

Sincerely,
adupuis

Edited by adupuis, 07 September 2010 - 07:17 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users