0

I need to create a large series of multiple machine numbers with the following character formatting: Machine Number(first 4 digits)_Date(last 6 digits) as provided in the sample image below:

enter image description here

One way I thought of solving this is using the concatenate function of excel (cause it's the only software I know so far for data analysis). However, concatenate is not enough in creating such a huge volume of data series considering that I need to create a series from January up to December in each machine number that ranges from 0001 up to 4001.

Is there any other way to automate this using Excel?

Improve this question
1
  • Do you also need the colors to be copied?
    – Ron Rosenfeld
    Commented Nov 25, 2015 at 1:24

1 Answer 1

Reset to default
2

The Concatenate() function is not very useful. In most cases, the & operator does the same thing with less typing.

Your screenshot sample seems to have errors. Machine number should be 0002 on the last row, but the Required Formatting column starts with 0001. Also, that cell does not show the date as in the previous column. You may want to update your sample to reflect what you really need.

If the first column is text and the next column is a date, then you can use

=A2&text(b2,"ddMMyyyy")

It is not clear if your regional setting use Day/Month/Year or Month/Day/Year, so if the latter, try

=A2&text(b2,"MMddyyyy")

Copy that formula down your list of data and don't worry about volume. Excel can handle over a million rows. With a simple formula like that you won't break it.

Edit: another scenario: Each column uses its own machine number. Use the formula in cell A1 and copy to the right for 4001 columns (for the machine numbers), then copy down for 365 rows (for the days)

=TEXT(COLUMN(A1),"0000")&TEXT(DATE(2015,1,ROW(A1)),"ddMMyyyy")

enter image description here

Improve this answer
12
  • I think you will run into a problem with the number of rows. A quick calculation says 4001 machines x 365 days = 1,460,365 rows. Excel will only do just over 1 million.
    – Rob Gale
    Commented Nov 24, 2015 at 7:00
  • @RobG put each machine number in a column. Excel supports over 16,000 columns x over a million rows. 4000 columns for the original machines, 4000 columns for the concatenated values in 365 rows. You'll need a bit of memory, but it's entirely possible.
    – teylyn
    Commented Nov 24, 2015 at 7:05
  • Hi teylyn, Thank you for taking the time to look at the problem. I already edited the sample screenshot. Actually, I wanted to automatically fill all the series starting from machine number '0001 up to '4001 concatenated to each day in the entire year.
    – Frances Ouano Ponce
    Commented Nov 24, 2015 at 7:05
  • @Rob G I think Excel can handle 1,460,000 rows.
    – Frances Ouano Ponce
    Commented Nov 24, 2015 at 7:06
  • @teylyn I gotta try your suggested formula. Thank you so much for the taking the time.
    – Frances Ouano Ponce
    Commented Nov 24, 2015 at 7:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .