excel - Formula To Remove Some, But Not All, Duplicates From A List -

-

i have list containing different words , numbers, sample of follows:

green blue 14 green yellow 11 yellow green green 14

i create parallel list removes every unique word/number every second instance of word/number - removes duplicates not all. continue above example, final list follows:

green 14 yellow green

as there 4 'green' in original list, there 2 in final list. there 2 'yellow' in original , 1 in final, , there single instances of blue , 11, these removed final list.

is there formula or methodology so? unfortunately haven't been able think of clever myself.

many in advance!

=or(countifs($a:$a,$a2)=1,int(countifs($a$2:$a2,$a2)/2)*2=countifs($a$2:$a2,$a2))

if it's either duplicate or unique, marked true. make sure delete true values @ once/disable automatic calculation, otherwise it'll mark odd duplicates after deleting prior occurrences.


Comments

Post a Comment

Popular posts from this blog

python - No exponential form of the z-axis in matplotlib-3D-plots -

-
Image
i have similar problem described in how prevent numbers being changed exponential form in python matplotlib figure : i don't want (in special case) weird scientific formatting of axis. problem different have problem @ z -axis. 2-d plots can use ax.get_yaxis().get_major_formatter().set_useoffset(false) . , there no function ax.get_zaxis() what use format z -axis same way? edit: example: from mpl_toolkits.mplot3d import axes3d import numpy np import sys import matplotlib import matplotlib.pyplot pyplot def func(xi, ti): res = 10e3 + np.cos(ti) * np.sin(xi) return res if __name__ == '__main__': timespacing = 20 timestart = 0 timeend = 1 time = np.linspace(timestart, timeend, timespacing) widthspacing = 50 widthstart = 0 widthend = 3 width = np.linspace(widthstart, widthend, widthspacing) reslist = [] matplotlib.rcparams['legend.fontsize'] = 10 fig = pyplot.figure() ax = fig.gca(projection = '3d...
Read more

excel vba - VBA Proxy auto-configuration for http requests -

-
i need build client web api in vba , needs work behind proxy (with authentication). i've been looking @ winhttp.winhttprequest , msxml2.xmlhttp/serverxmlhttp classes. turns out that: xmlhttp automatically detects proxy settings provided through proxy.pac file (good) winhttprequest doesn't (bad) however, on other hand: xmlhttp automatically follows redirects, , there's no way of disabling behaviour (bad) winhttprequest doesn't (good) since i'd able have cake , eat it, there way automatic proxy configuration component such winhttprequest doesn't follow redirects blindly? the vba-web project might pastry eating problem. https://github.com/vba-tools/vba-web i guess wish go like: dim client new webclient client .baseurl = "https://www.google.com" .proxyusername = <user> .proxypassword = <password> .enableautoproxy = true end dim request new webrequest request .method = webmethod.httpget ...
Read more

sql server 2008 - split ssrs expression between 2 words -

-
i have requirement split result 3 separate expressions, using sql server 2008. the result example. "person: man tel: yes rel: christian msg: misc text" i need split into: "person: man" "tel: yes" "rel: christian" "msg: misc text" i have been using iif statement return bit before "tel" cant return between tel , rel etc. part before colon remain static whichever result returned in dataset, part after colon (of length too) before getting next section. can please? other similar questions on forum have been helpful first expression, later ones little more difficult. for sql method works test did: select left(txt, charindex('tel:',txt)-2) part1, substring(txt, charindex('tel:',txt), charindex('rel:',txt)-charindex('tel:',txt)) part2, substring(txt, charindex('rel:',txt), charindex('msg:',txt)-charindex('rel:',txt)) part3, right(txt, len(txt...
Read more