Here is a quick Monday tip for data junkies.
If you want to shuffle a list of numbers or cells in excel in random order you can do that with “data > sort” menu option of excel.
- First insert a column next to the list you want to shuffle and enter random numbers in each cell in using
=rand(). See below:
- Now select the entire list and sort it by the new column with random numbers. (Menu > data > sort)
- If you want a fresh set of shuffled list, just hit f9 (recalculates all formulas) and sort again
Like this? Also learn how to sort horizontally, fill only weekdays while auto-filling dates
One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”
Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.
I added a new sheet, and named the ranges for Sara and James.
Maybe something like:
B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))
Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.