Want to fetch the Rows data into columns
- Thread starter Areif
- Start date
p45cal
Well-Known Member
See attached.
Both Sheet1 and Sheet2 have been processed.
1. Add a cell at the bottom of column A with just MZ in it (A26 and A253 in those sheets (green coloured)).
2. See the green cells at Sheet1 B2:J2 and Sheet2 B1:N1. In Excel 2007 you need to be careful how you enter the formula:
For Sheet2: Select the cells from B1 to N1, a single row, at least wide enough to take the highest number of data cells for each group, then paste or type in the fomula:
then rather than just pressing Enter to commit the formula to the sheet, you need to hold the Ctrl & Shift keys while you press Enter. This will array-enter the formula in all that row of cells at once. Note the 255 may need to be adjusted to accommodate all your rows of data.
Once that's done you need to drag/copy down that range (B1:N1) down to the bottom row. The result, hopefully, is what you want, but with lots of blank rows inbetween . To close up the spaces, it's easiest to copy, paste-special|Values the results to another area of the sheet where we can sort the results. This will result in all the blank rows being sorted to the top (or bottom). Then you can delete all the rows you don't want (they appear to be blank but actually hold an empty string ("")).
If you want to retain the result rows in the same order as they appear in the source data, you can sort twice: First, after copy paste-special|Values, manually add a new index column next to the results, numbering from 1, 2, 3, 4 etc., sort first by left most column of your copied data, then select only the area with data in, and sort again by the index column, then you can delete the index column. I've done this on both sheets of the attached; at P6 for Sheet1 and P1 for Sheet2.
That's it.
All this is quite intensive work and you have to be careful. Looking at your data, it seems it's been pasted from somewhere else and Excel has tried to be helpful by interpreting what look like code numbers to actual numbers, losing any leading zeroes along the way. Also there's a varying amount of data per group. I suspect that this data might be more easily imported directly from the source data (a csv file or somesuch?). If this is the case a macro would better be able to import this data without Excel mangling it. If you have the source data file, perhaps attach it here?
Both Sheet1 and Sheet2 have been processed.
1. Add a cell at the bottom of column A with just MZ in it (A26 and A253 in those sheets (green coloured)).
2. See the green cells at Sheet1 B2:J2 and Sheet2 B1:N1. In Excel 2007 you need to be careful how you enter the formula:
For Sheet2: Select the cells from B1 to N1, a single row, at least wide enough to take the highest number of data cells for each group, then paste or type in the fomula:
Code:
=IFERROR(IF(LEFT(A1,2)="MZ",TRANSPOSE(INDEX(A:A,ROW()):INDEX(A:A,MATCH("MZ*",A2:A$255,0)+ROW(A1)-1)),""),"")Once that's done you need to drag/copy down that range (B1:N1) down to the bottom row. The result, hopefully, is what you want, but with lots of blank rows inbetween . To close up the spaces, it's easiest to copy, paste-special|Values the results to another area of the sheet where we can sort the results. This will result in all the blank rows being sorted to the top (or bottom). Then you can delete all the rows you don't want (they appear to be blank but actually hold an empty string ("")).
If you want to retain the result rows in the same order as they appear in the source data, you can sort twice: First, after copy paste-special|Values, manually add a new index column next to the results, numbering from 1, 2, 3, 4 etc., sort first by left most column of your copied data, then select only the area with data in, and sort again by the index column, then you can delete the index column. I've done this on both sheets of the attached; at P6 for Sheet1 and P1 for Sheet2.
That's it.
All this is quite intensive work and you have to be careful. Looking at your data, it seems it's been pasted from somewhere else and Excel has tried to be helpful by interpreting what look like code numbers to actual numbers, losing any leading zeroes along the way. Also there's a varying amount of data per group. I suspect that this data might be more easily imported directly from the source data (a csv file or somesuch?). If this is the case a macro would better be able to import this data without Excel mangling it. If you have the source data file, perhaps attach it here?