Apologies if this is in wrong section.
I need some pivoting help, not necessarily a pivot table though. Columns A-C are what I currently have, and the other columns are what id like it to look like. (in this case, it has employee ids, sales, and what year they did those sales). Any formula suggestions are helpful.
F2=IFERROR(INDEX($A$2:$A$100,SMALL(IF(FREQUENCY(IF($A$2:$A$100<>"",MATCH($A$2:$A$100,$A$2:$A$100,0)),ROW($A$2:$A$100)-ROW($A$2)+1),ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($A$2:A2))),"")
Control+shift+enter
copy down
G1=IFERROR("Year"&" "&INDEX($C$2:$C$100,SMALL(IF(FREQUENCY(IF($C$2:$C$100<>"",MATCH($C$2:$C$100,$C$2:$C$100,0)),ROW($C$2:$C$100)-ROW($C$2)+1),ROW($C$2:$C$100)-ROW($C$2)+1),COLUMNS($G$1:G1))),"")
Control+shift+enter
copy across
G2=IF(AND(G$1<>"",$F2<>""),SUMIFS($B$2:$B$100,$A$2:$A$100,$F2,$C$2:$C$100,TRIM(SUBSTITUTE(G$1,"Year","")+0)),"")
Copy across and down
Last edited by CARACALLA; 10-30-2020 at 06:43 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks