Hi,
I have a monthly report which shows our internal tickets and corresponding vendor tickets. Some of our internal tickets may not have a vendor ticket or may have only one vendor ticket, while others will have two or more vendor tickets. I've included a sample of the data below. I want column E to show the vendor tickets and to consolidate rows with 2 or more vendor tickets to one row.
I was thinking there might be a way to do with this a function if I had the count of vendor tickets per internal ticket, so I created the following function in column B. This probably won't be necessary for the macro, but here it is just in case. =IF(AND(C2=C1,ISNUMBER(B1)=TRUE),B1+1,IF(AND(C2=C1,C2=C3,ISNUMBER(B1)=TRUE),B1+1,IF(C2=C3,1,"")))
The amount of data will vary every month, so the macro will have to be able to handle varying numbers of rows. I'm also including this step as part of a larger macro to format the data, so any additional information on incorporating a macro for this purpose would be helpful.
<tbody>
</tbody>
Thank you, in advance, for your time and assistance!!
I have a monthly report which shows our internal tickets and corresponding vendor tickets. Some of our internal tickets may not have a vendor ticket or may have only one vendor ticket, while others will have two or more vendor tickets. I've included a sample of the data below. I want column E to show the vendor tickets and to consolidate rows with 2 or more vendor tickets to one row.
I was thinking there might be a way to do with this a function if I had the count of vendor tickets per internal ticket, so I created the following function in column B. This probably won't be necessary for the macro, but here it is just in case. =IF(AND(C2=C1,ISNUMBER(B1)=TRUE),B1+1,IF(AND(C2=C1,C2=C3,ISNUMBER(B1)=TRUE),B1+1,IF(C2=C3,1,"")))
The amount of data will vary every month, so the macro will have to be able to handle varying numbers of rows. I'm also including this step as part of a larger macro to format the data, so any additional information on incorporating a macro for this purpose would be helpful.
A | B | C | D | E |
DATE_SUBMITTED | Internal | Vendor | Vendor Consolidated | |
6/1/2013 6:15 | 75471 | 70061 | ||
6/2/2013 10:53 | 75472 | |||
6/8/2013 23:32 | 75552 | |||
6/2/2013 15:32 | 75661 | |||
6/3/2013 11:59 | 75662 | 70209 | ||
6/5/2013 5:23 | 75752 | |||
6/2/2013 19:56 | 75861 | |||
6/5/2013 10:54 | 75863 | |||
6/5/2013 22:08 | 75864 | 70230 | ||
6/6/2013 6:33 | 75865 | 70269 | ||
6/7/2013 14:09 | 75866 | |||
6/3/2013 8:55 | 75964 | |||
6/2/2013 13:01 | 76046 | |||
6/3/2013 19:05 | 76047 | |||
6/3/2013 12:30 | 76146 | 70212 | ||
6/4/2013 6:16 | 76147 | 70209 | ||
6/4/2013 6:16 | 1 | 76148 | 70215 | |
6/4/2013 6:16 | 2 | 76148 | 70259 | |
6/4/2013 6:16 | 3 | 76148 | 70272 | |
6/4/2013 6:39 | 76149 | |||
6/4/2013 11:24 | 76150 | 70232 | ||
6/4/2013 12:36 | 76151 | |||
6/5/2013 6:16 | 76153 | 70234 | ||
6/5/2013 6:16 | 76154 | 70236 | ||
6/5/2013 6:54 | 76155 | 70246 | ||
6/5/2013 7:42 | 76156 | |||
6/5/2013 12:45 | 1 | 76157 | 70249 | |
6/5/2013 12:45 | 2 | 76157 | 70254 | |
6/5/2013 15:03 | 76159 | 70239 | ||
6/6/2013 6:15 | 76160 | 70247 | ||
6/6/2013 9:27 | 76162 | 70268 |
<tbody>
</tbody>
Thank you, in advance, for your time and assistance!!
Last edited: