LinkBack URL
About LinkBacks
Hello,
Please see attached
I need to extract unique data from Column B to Column J (sorted by Code) and Assign a Code to Column K
Code is driven by LEADINGS Spaces in B
I need unique data from B, trimmed, with larger Codes
Thanks all
Hello,
Please see attached
I need to extract unique data from Column B to Column J (sorted by Code) and Assign a Code to Column K
Code is driven by LEADINGS Spaces in B
I need unique data from B, trimmed, with larger Codes
Thanks all
Hi ionelz,
If you use the blank column C and make it Col B trimmed then a Pivot Table will do what you want, if you sort by the code. See the attached.
Trimmed Pivot Sort by Count.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
I would preferred a Formula, and Code is manual typed by me, I need a formula for code too, thanks for lookingOriginally Posted by MarvinP
All I know is B
Hi ionelz,
If you had the newer Excel 365 you could use the Unique() and Sort() functions. You show an older Excel 2016 so I don't think they are available to you.
See this site to see if it gets you closer to your answer.
https://www.thespreadsheetguru.com/b...20end%20result.
Yes, I hope to get new Excel soon...
Until then need long formula for extract and sort
Here, I need to determine Code Formula in F based on numbers in E
Then to extract unique, excluding lower code
This proposal employs a helper column (G) which may be moved and/or hidden for aesthetic purposes.
The Code column (F) is populated using**: =SUM(IF(E3>$E$3:$E$13,1/COUNTIF($E$3:$E$13,$E$3:$E$13)))
**Denotes an array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
The helper column is populated using: =IF(F3=AGGREGATE(14,6,F$3:F$13/(TRIM(B$3:B$13)=TRIM(B3)),1),F3,"")
The Trimmed Data column (J) is populated using: =IF(K3="","",TRIM(INDEX(B$3:B$13,AGGREGATE(15,6,(ROW(B$3:B$13)-ROW(B$2))/(G$3:G$13=K3),COUNTIFS(K$3:K3,K3)))))
The Code column (K) is populated using: =IFERROR(SMALL(G$3:G$13,ROWS(A$1:A1)),"")
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks