Hi. I've been searching for answers and trying sketchy code, but I can't get anything to work for me.
I have a spreadsheet with over 10 thousand rows of data. Each row shows the ship date of the serial number, or numbers, that shipped that day. Some days we shipped one SN. Other days we shipped up to 32 SNs. All of the SNs are listed on one row for the day they shipped.
I need code that will look to see if there is more than one SN that shipped that day and put each SN on its own row with the associated row data (Col A thru Col K). The SNs start at Col L and extend to Col AQ (but may be more in the future). The Ship Date column is Col I. Row one is the header row (this needs to stay intact). After the transpositioning is done, it needs to delete duplicate rows where all of the data (Col A thru Col L) in that row matches.
Thank you in advance for your help!
I have a spreadsheet with over 10 thousand rows of data. Each row shows the ship date of the serial number, or numbers, that shipped that day. Some days we shipped one SN. Other days we shipped up to 32 SNs. All of the SNs are listed on one row for the day they shipped.
I need code that will look to see if there is more than one SN that shipped that day and put each SN on its own row with the associated row data (Col A thru Col K). The SNs start at Col L and extend to Col AQ (but may be more in the future). The Ship Date column is Col I. Row one is the header row (this needs to stay intact). After the transpositioning is done, it needs to delete duplicate rows where all of the data (Col A thru Col L) in that row matches.
Thank you in advance for your help!
Sandbox.xlsm | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | C1 | C2 | C3 | C4 | C5 | C6 | C7 | C7 | C8 Ship Date | C9 | C10 | SN01 | SN02 | SN03 | SN04 | SN05 | SN06 | ||
2 | 2082616327 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 11/21/2016 | ABC194142 | 15 | 6847 | |||||||
3 | 2082616327 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 11/21/2016 | ABC194142 | 15 | 6848 | |||||||
4 | 2082616328 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 11/21/2016 | ABC194142 | 1 | 6807 | 6822 | 6823 | 6838 | ||||
5 | 2083914104 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 11/21/2016 | ABC194142 | 1 | 6082 | 6364 | 6528 | 6667 | 6684 | 6710 | ||
6 | 2083917066 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 11/21/2016 | ABC194142 | 1 | 3976 | 5524 | 5730 | 5790 | 5827 | 6016 | ||
7 | 2082616328 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 11/30/2016 | ABC194142 | 22 | 6866 | 6867 | 6868 | 6869 | 6871 | 6872 | ||
8 | 2082616328 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 11/30/2016 | ABC194142 | 15 | 6837 | 6849 | 6850 | 6851 | 6852 | 6853 | ||
9 | 2082616328 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 11/30/2016 | ABC194142 | 4 | 6854 | 6857 | 6859 | 6860 | 6861 | 6862 | ||
10 | 2082616328 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 11/30/2016 | ABC194142 | 6 | 6874 | 6881 | ||||||
11 | 2030382311 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 12/06/2016 | ABC194142 | 1 | 1735 | |||||||
12 | 2037908237 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 60 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 12/06/2016 | ABC194142 | 1 | 1903 | |||||||
13 | 2055620800 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 12/09/2016 | ABC194142 | 1 | 4286 | |||||||
14 | 2082616355 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 12/20/2016 | ABC194142 | 8 | 6930 | 6931 | ||||||
15 | 2082616355 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 12/21/2016 | ABC194142 | 6 | 6864 | 6870 | 6875 | 6876 | 6879 | 6880 | ||
16 | 2082616355 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 12/21/2016 | ABC194142 | 2 | 6865 | 6873 | 6874 | 6877 | 6882 | 6883 | ||
17 | 2085922992 | CUSTOMER SUPPORT | 343762 | 104170LMN613860 | 001 | 4XDR50-RV13 | 4XDR50-RV13-214853 | GHI | 12/23/2016 | ABC343762 | 16 | 6905 | |||||||
18 | 2082616355 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 12/21/2016 | ABC194142 | 2 | 6893 | 6895 | 6902 | 6903 | 6904 | 6906 | ||
19 | 2062992652 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 01/23/2017 | ABC194142 | 1 | 5300 | |||||||
20 | 2055617837 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 01/24/2017 | ABC194142 | 1 | 3444 | |||||||
21 | 2066705244 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 01/24/2017 | ABC194142 | 1 | 3688 | |||||||
22 | 2067457837 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 01/18/2017 | ABC194142 | 1 | 3904 | |||||||
23 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 01/24/2017 | ABC194142 | 12 | 6993 | 6994 | 6995 | 6996 | 6997 | 6998 | ||
24 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 01/26/2017 | ABC194142 | 12 | 7013 | 7014 | 7015 | 7016 | 7017 | 7018 | ||
25 | 2030385244 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 01/24/2017 | ABC194142 | 1 | 3764 | |||||||
26 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 01/26/2017 | ABC194142 | 12 | 7021 | 7023 | 7024 | 7025 | 7026 | |||
27 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 01/26/2017 | ABC194142 | 7 | 7000 | 7001 | ||||||
28 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 01/27/2017 | ABC194142 | 9 | 6069 | 6858 | 6898 | 6910 | 6911 | 6912 | ||
29 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 01/30/2017 | ABC194142 | 5 | 7002 | 7003 | 7004 | 7005 | 7006 | |||
30 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 01/30/2017 | ABC194142 | 2 | 7009 | 7010 | ||||||
31 | 2076604504 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 01/30/2017 | ABC194142 | 1 | 5255 | |||||||
32 | 2074657837 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 02/02/2017 | ABC194142 | 1 | 4346 | |||||||
33 | 2072554133 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 01/30/2017 | ABC194142 | 1 | 5006 | |||||||
34 | 2072551170 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | ABC | 01/30/2017 | ABC194142 | 1 | 5815 | |||||||
35 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 01/30/2017 | ABC194142 | 8 | 7011 | 7012 | ||||||
36 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 01/30/2017 | ABC194142 | 5 | 6899 | 6900 | 6901 | 6915 | 6916 | 6920 | ||
37 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 01/31/2017 | ABC194142 | 2 | 7028 | 7029 | ||||||
38 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 02/03/2017 | ABC194142 | 2 | 7037 | 7039 | 7041 | 7045 | 7046 | |||
39 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 02/03/2017 | ABC194142 | 8 | 7047 | 7048 | 7049 | |||||
40 | 2082616356 | CUSTOMER SUPPORT RAILROAD | 194142 | 58830LMN346682 | 10 | 4XDR50-RV13 | 4XDR50-RV13-121340 | JKL | 02/03/2017 | ABC194142 | 2 | 6908 | 6917 | 6918 | 6923 | 6924 | 6925 | ||
Sandbox |