Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
grecoaica pe Simpatie.ro
 | Femeie
22 ani
Dambovita
cauta Barbat
22 - 45 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
I received a database with 10000+ rows which for each name has subtotals and also has the name only for the first row of data corresponding to that name, the rest being blank. If I want to make a Pivot, I need this to have all rows completed. The name column and subtotal rows have the color yellow, while the table is blue.
If I try to select the column, on the Home ribbon - Go to Find & Select - Go to Special - Blanks, it will say there are no blanks in selection. That is because of the background fill of the cells. The name is also on the subtotals row, so I have to find a different way to remove these subtotal rows. Subtotal names are in column E, while subtotal values are in columns H and J. The rest of the cells on subtotal rows are blank, except for the yellow formatting.
1. Select the desired range and remove fill - in my case the E column
2. Create a helper column to the right and type, for example: =IF(A2="";"DELETE THIS ROW";"")
2.1. If current order matters, create another helper column to the right and write a sequence of numbers from 1 to n where n is the total number of rows in your data (number all your rows incrementally to be able to sort from Small to Large later)
3. Sort Z to A on the first helper column, to have all the "DELETE THIS ROW" values on top
4. Delete the rows containing "DELETE THIS ROW" and sort the data with your second helper column.
5. Select the column with blank (in my case column E) on the Home ribbon - Go to Find & Select - Go to Special - Blanks, now the blank cells should be selected
6. While selected, press = and the first blank cell in the range will let you enter a formula. After = enter the address of the cell immediately above, for example in my case =E2 (first blank cell is E3)
7. IMPORTANT! Instead of simply pressing ENTER, press CTRL+ENTER.
Note: this works on multiple rows and columns at the same time, provided that blank cells are selected.
Source:
|
|
| pus acum 1 luna |
|