I have an Excel spreadsheet which is tens of thousands of entries long but it's not organized.  All the data is in a single column with each entry taking up 3 rows followed by a space.  For example:  Entry 1 Name Entry 1 Address Entry 1 Phone  Entry 2 Name Entry 2 Address Entry 2 Phone   This goes on for about half a million entries.  I need to organizing it better and would like to transpose it to look like this  Entry 1 Name      Entry 1 Address      Entry 1 Phone Entry 2 Name      Entry 2 Address      Entry 2 Phone   I've been trying to figure out how to write a formula that I can auto fill in Excel but I can't figure it out.  I'm using MS Office on OSX.  But any solution be it Excel or non Excel would be great.  Thanks!
43621
I have a movie list with over 1500 entries (A1-A1500), all alphabetized (Thanks to Excel) and I would like to split that into multiple columns of 55 rows each on a separate sheet if possible.  So A1-A55 from Sheet1 would go to A1-A55 on Sheet2, and A56-A110 would go to B1-B55 on Sheet2, so on and so on.  Each cell is an individual movie name and I would just like them to go from one long column to multiple columns with 55 rows in each.
86051
This question is similar, yet different, to another DBA SE question I have here:     I have a FINANCE_SOURCE table:  +-----------+------------+-------------+--------+------+ | SOURCE_ID | PROJECT_ID | SOURCE_NAME | AMOUNT | YEAR | +-----------+------------+-------------+--------+------+ |       101 |          1 | A           |  10.00 | 2017 | |       102 |          1 | B           |   5.00 | 2017 | |       103 |          1 | B           |  15.00 | 2017 | |       104 |          1 | B           |  70.00 | 2016 | |       105 |          1 | C           |  30.00 | 2017 | |       106 |          1 | D           |   1.00 | 2016 | |       107 |          1 | D           |  20.00 | 2017 | +-----------+------------+-------------+--------+------+   I want to rearrange the data, so that SOURCE_A, SOURCE_B, and OTHER(anything that's not A or B) are in separate columns.  The data needs to be grouped into separate YEARS:  +------------+---------+---------+-------------+-------+------+ | PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR | +------------+---------+---------+-------------+-------+------+ |          1 |      10 |      20 |          50 |    80 | 2017 | |          1 |         |      70 |           1 |    71 | 2016 | +------------+---------+---------+-------------+-------+------+   How can I do this? Performance is important.
112223
How do I add a list of names to a spreadsheet and each time a specific name appears add the number 10 to cell.  For example, I would put a name like Jones in A1 then Jones in A5. And in D5 it would have the number 20. If I then add Jones in B2 then D5 would say 30. Add 10 each time Jones appears.  What   formula  would I use?
81181
I have data like so:  1001NCCN    3618127 1001NCCN    208478 1001NCCN    207316 1001TEMN    409889 1001TEMN    801651 1001TEMN    273134 1001TEMN    208478 1001TEMN    207316   I need to transpose/combine the rows that have matching values in the first column with a final result like so:   1001NCCN    3618127 208478  207316       1001TEMN    409889  801651  273134  208478  207316   I looked at Pivot Tables, and filtering but neither seemed like they can give me what I need.  Is there a way to do this within Excel?
37670
This problem is simple to correct but is very very annoying what I'm doing is to type a hour range, eg. (0-1),(1-2),(2-3) so on per cell row     DATE ---   HOUR    1/1/2013  0-1  1/1/2013  1-2 1/1/2013  2-3 1/1/2013  3-4   Excel stupidly keeps formatting 1-2 to a date 2-Jan to make matters worse is when I change it to a number format it gives 41276  I also cannot find the AutoFormat option in excel 2010, I am aware of the Format button in home but there is no autoformat there.
60487
I have this input:  sdkxyosl 1 safkls 2 asdf--asdfasxy_asd 5 dkd8k  jasd 29 sdi44sw 43 asasd afsdfs 10 rklyasd 4   I need this output:  sdi44sw 43 dkd8k  jasd 29 asasd afsdfs 10 asdf--asdfasxy_asd 5 rklyasd 4 safkls 2 sdkxyosl 1   So i need to sort the lines by the last column.  I don't know how many columns are in one line.  I just can't figure it out, how to do it. I don't have "perl powers". I just have ~average scripting powers with sed, awk, cut, etc..   Does somebody know how to do it?
50643
I have a spreadsheet in the newest iWork Numbers.app, with something like:  CATEGORY | POINTS         HW | 100       HW | 100       NW | 12       HW | 50       NW | 23   I want to just sum up all the Category = HW into a cell: I'm not sure how. I think I need to use the SUMIF() function but I get stuck afterwards.
82081
In LibreOffice Calc, if I have a column with a lot of numbers in this format 8,1 (comma), how  can I transfer all these numbers to the format 8.1 (dot)?  When I say "a lot" I mean thousands of numbers, so it is just impossible to rewrite it again in the right format.
42644
I have more than 10,000 rows of data I need to do some calculations on one column of the data. I have made the calculation in one cell and now need to fill the formula for the calculation and all the cells.  Usually I just select the cells using the keyboard and then press the Ctrl+D to fill the cells. However, now I have to do this repeatedly, and it is difficult to keep on selecting 5000 rows each file. Is there a simpler way where I can just specify the range using the keyboard? Perhaps even have a macro since all of the files have a fixed length  Also, since I have difficulty in using the mouse because of medical reasons and therefore I have not tested  which involves clicking on a very small fill handle. Maybe there is a parallel solution to this using the keyboard or maybe I can create a macro which will automatically click on a fill handle of a selected cell - please advise if there is a way.
69584
I have multiple items that are available in multiple distribution centers (i.e., a many-to-many relationship). There is currently one row per item, with one column for each distribution center. A cell in the row for item X and the column for distribution center Y is marked with the code for distribution center Y if item X is available there and blank otherwise. An item with multiple distribution centers will have multiple distribution center codes (in their respective columns). So the current sheet looks like:      |   A    |         B         |*|                      S-AJ                      | 1   |  ID #  |   Description     |…|              Distribution Centers              | 2   |   17   |   Ginkgo Biloba   |…|      |      |      |      |      |      |  SE  | 3   |   42   |   Ginseng         |…|      |  MP  |  MS  |      |  NW  |      |      |                      ︙   Columns C through R contain other attributes of the items, such as UPC code, cost, and price, which are not relevant to this question. My actual sheet has 18 distribution centers, spanning columns S through AJ; I reduced that to get the example to fit into Stack Exchange’s window.  I need to have a single distribution center column, with a single distribution code per row, and then duplicate the rows as needed for items that currently contain multiple codes. The result should look like:      |   A    |         B         |*|   S  | 1   |  ID #  |   Description     |…|  DC  | 2   |   17   |   Ginkgo Biloba   |…|  SE  | 3   |   42   |   Ginseng         |…|  MP  | 4   |   42   |   Ginseng         |…|  MS  | 5   |   42   |   Ginseng         |…|  NW  |                      ︙   where cells A3:R3, A4:R4, and A5:R5, contain the same information.  The only way I can think of doing this, which would be time consuming, would be to copy the item number into multiple rows; and in the column that has the distribution code I would change code for the item that is available in each distribution center. I will be doing this for 900 items. Is there an easier way to do this?
28744
I would like to compare two Excel spreadsheets  Both have the same columns, in the same order, but not the same rows.  I have a spreadsheet with 1,000 rows and 50 columns including a unique ID for each row and personal data (address, phone number, etc.).  I want to compare it with the same base extracted one year ago.  My objective is to know all changes (for example, someone who changed his address).  I tried with spreadsheet (as explained here: ) but it doesn't work because in my "new" file I have new rows, and I have some rows in the old file which are not in the new.  Spreadsheet compare with Excel line number. Is it possible to compare with the first column (Unique ID)?   Here is a very simplified example:    There are two sheets representing my two spreadsheets.  As you can see both have the same columns but:    There are rows that are on both sheets with no changes (all the row is the same)  There are rows that are on both sheets but there are changes (in orange on my example)  There are rows that are only in the December sheet (I don't need to identify these lines)  There are rows that are only in the January sheet (in orange on my example)    The orange color is exactly what I want, but in this example I can do it manually: comparing December and January. In the real spreadsheets I cannot do it manually as there are too many changes, data, lines, columns, etc. and I will do this monthly.   There is only one column which can never change: the column A.
37884
I have hundreds of excel files which are all of the same format (i.e. 4 worksheets per Excel file). I need to combine all the files into 1 all singing and dancing file which must have the same format as the originals (i.e. maintain the four separate worksheets, which are all identically named).  While each file is structured the same, the number of columns (and the heading names) between sheet 1 and 2 (for example) is different. So it can’t be combined into one file with everything in one sheet!  There are two complications:   I need to create an EXTRA column in the merged file (on EACH sheet) to identify the source file (“file name”). The files contain a lot of zero data entries (e.g. 55 rows of useful data followed by hundreds of rows of zeros) which I need to remove from the merged file.   I’ve never used VBA, but everyone has to start somewhere I suppose.
26464
I have a spreadsheet that I need to add new data to but the previous user made it so that I can't add data in a new row without moving down a lot of data which would mess with all the calculations.  What I am trying to do is to make it so I can add the new data to the side instead and still have the same drop down access the two separate columns. I attached a screen cap of an example of what I want below.  I have tried everything I can think of and all my searches come back with putting multiple columns in a drop down instead of just the data from them.  As you can see below the example has data in D1 to D5 and F1 to F5. If I wanted the dropdown to have the data from those columns how would I do it?  EDIT: Just to clarify there is data between the columns in the actual page which is why I can't just highlight all of it.
45653
I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with multiple key staff names listed in the same cell, separated by line breaks (ie CHAR(10) ). There is one business per row. The number of lines in the 'key staff' cell varies row-by-row.  My initial sheet looks like this:    I need to do the following to optimize this sheet:   split each multi-line ‘key staff’ cell so each key staff name appears on its own row. This requires that new row/s be inserted beneath the original row. duplicate the data from all other cells on the original row (i.e. from columns A &amp; C:E), so that each new row contains the full data for each business I need an automated process - I'll have about 1000 businesses to process so can't do this with any manual steps   The sheet should then look like this:    Using =TRANSPOSE(SPLIT(B1,CHAR(10))) obviously only goes part of the way - it doesn’t insert new rows and doesn’t duplicate the surrounding column entries. All help gratefully accepted!
41506
I have an Excel spreadsheet with 1 column, 700 rows. I care about every seventh line. I don't want to have to go in and delete the 6 rows between each row I care about. So my solution was to create another sheet and specify a reference to each cell I want.  =sheet1!a1 =sheet1!a8 =sheet1!a15   But I don't want to type in each of these formulas ... `100 times.I thought if I selected the three and dragged the box around, it would understand what I was trying to do, but no luck.  Any ideas on how to do this elegantly/efficiently?
26622
Anyone know of a way to reformat either the whole txt file or the selection (I don't really care, either is fine) so that it would would add spaces to align text as columns?  example:  Green 5 Vest Black 6.93 Scarf Red 35.6 Hat   so that it ends up like:  Green 5    Vest Black 6.93 Scarf Red   35.6 Hat   or something similar... I mean the important thing is that they are arranged in an easy-to-read fashion.
38248
I have three tables and i need an out put which has data from two tables, i would like to create the same using macro.  Table A  A B C D   Table B  Apple Orange Pears   Table C  Americas Asia Europe   The Output i need is  A Apple Americas A Apple Asia A Apple Europe A Orange Americas A Orange Asia A Orange Europe   So i guess for each letter from Table A, there would be 12 additional rows created. The above is the sample data and I have around 5000 rows in Table A.