Hello,
I have a long spreadsheet with booked journals (up to 5000 lines). I would like to compare the journals by given period with another spreadsheet. I managed to make a function returning result only if the period matches. Now I need this data to be sorted in a new column so that the cells containing data are on the top and blank cells are below. I tried to skip the empty cells with IF function, but that duplicates journal references. Also tried SMALL function but this sorts data only if it is a number. The journal reference can either start with a number or with the initals of the person + the number (and I need a function which will not alter this reference). It doesn't really matter how the function sorts the journal reference the main objective is to ignore blank cells. I'm aware that the esiest way would be to use "custom sort" but I would like to get the same result with a function. Attached you may find the example of how the data will look like and what is the desired result. I hope the explanation is clear.
Thank you in advance for any help.
Klara
Last edited by Klara; 10-06-2011 at 07:23 AM.
Hi,
I found 2 solutions for this.
First solution is RAND function which assigns a number between 0 and 1 to the journal reference. Sorted the numbers with SMALL function and VLOOKUP-d back the journal reference in a new column (see attachment "RAND_solution.xlsx"). The random number changes with every change in the worksheet so this solution is not useful for my case but someone might benefit from it.
I ended up using the second solution, CODE function. CODE assigns a number for the first character in the cell. In my case the journal numbers start with the same number or letter so I added +1 to the first row +2 to the second etc etc. to get unique numbers. Sorted the numbers with SMALL function a VLOOKUP-d back the journal reference (see attachment "CODE_solution.xlsx"). I added number 1 to the first cell in the column with the CODE function as SMALL skips the smallest number and sorts from the second smallest.=IF(G2<>"",RAND(),"") =IF(ISERROR(SMALL(F1:F15,ROW())),"",SMALL(F1:F15,ROW()))
It's probably not the most elegant solution but fulfilled my requirements. If anyone got a better solution please let me know.=IF(ISERROR(CODE(G2)),"",(CODE(G2)+1)) =IF(ISERROR(SMALL(F1:F15,ROW())),"",SMALL(F1:F15,ROW()))
Klara
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks