combine rows with duplicate values and get total count of duplicate

[PA_VA13]

Hi sorry that my data is not in XL2BB but I still have not figured out why it stopped working on my PC.

I am trying to figure out how to create a button that would combine rows B-F dependent on the duplicate values in column B into a single row in columns H-M, but also I want the code to count the total number of times the value in column B is duplicated into another column labeled "# Blocks" in column I.

I have not been able to find any tutorials on how to do this.

Note: (column B data is created by the formula =LEFT(A2,MIN(--(FIND(CHAR(ROW($65:$90)),A2&CHAR(ROW($65:$90)),3)))-1) with data taken from column A).


I want to take the initial data

and condense it into this format

 

[Peter_SSs]

Is it possible for you to upload a copy of the file (with any sensitive data removed/disguised), or a small sample dummy file that displays similar errors for you, to DropBox, OneDrive etc and provide a shared link here? That way we could get a better idea of whether it is your actual data or your machine that might be the issue.

 

[PA_VA13]

Is it possible for you to upload a copy of the file (with any sensitive data removed/disguised), or a small sample dummy file that displays similar errors for you, to DropBox, OneDrive etc and provide a shared link here? That way we could get a better idea of whether it is your actual data or your machine that might be the issue.

Would Google Drive work? If not you can send me a private message and I can email it to you.

 

[Peter_SSs]

Would Google Drive work? If not you can send me a private message and I can email it to you.

Google Drive should be fine if you provide a shareable link. Please post the link(s) here as #4 of the Forum Rules prohibits using private messages or email to take questions off-forum.

 

[PA_VA13]

My apologies. I was getting so frustrated with our systems and trying to get you guys the information so that you can actually look at the data to further assist me that I completely forgot about forum rule #4. ?

On the plus side I was able to get OneDrive uploaded and running on my PC. Hopefully our system allows you to have access to the file.

https://dvagov-my.sharepoint.com/:x...xJoj6V3nuWbesBAWhjedt3gvM33xXkdbdUiw?e=gs4e1A

 

[PA_VA13]

My apologies. I was getting so frustrated with our systems and trying to get you guys the information so that you can actually look at the data to further assist me that I completely forgot about forum rule #4. ?

On the plus side I was able to get OneDrive uploaded and running on my PC. Hopefully our system allows you to have access to the file.

https://dvagov-my.sharepoint.com/:x...xJoj6V3nuWbesBAWhjedt3gvM33xXkdbdUiw?e=gs4e1A

Google Drive should be fine if you provide a shareable link. Please post the link(s) here as #4 of the Forum Rules prohibits using private messages or email to take questions off-forum.

Welp OneDrive is a bust. Apparently when I upload the file into OneDrive on our PCs it completely erases all of my code and buttons. ? I'm going to see if I can load Excel on my phone and install the XL2BB there. Fingers crossed that that route will work.

 

[PA_VA13]

Google Drive should be fine if you provide a shareable link. Please post the link(s) here as #4 of the Forum Rules prohibits using private messages or email to take questions off-forum.

Let's see if this link will work

Copy of Merge Tab.xlsm

drive.google.com

 

[Peter_SSs]

Let's see if this link will work

It does, thanks.

I don't think that your PC has gremlins but there are several issues to resolve.

1. In post 1, column B has "ID (Accession #) without Cassettes" data. In your uploaded file, rows 2-15 where your data is does not have anything.
   
   
2. In post 9 I said:

   You will get that error if there are any error values resulting from your column B formula. One way that would happen is if you have any cells in column A that contain less that 2 characters.
   Could that be the problem?

   You never addressed that point and yet your column B is virtually full of error values because column A in those rows (rows 16:1146) has less than 2 characters!
   
   
3. In post 1 the table goes from columns A:G with column C being '# Blocks'. In your uploaded file the data only goes from columns A:F with no '# Blocks' column at all.
   
   
4. In post 1 the section from col H contains 6 columns with '# Blocks' being the second of the 6. In you uploaded file the section from col H contains 7 columns with '# Blocks' being the third of the 7.
   
   
5. In your uploaded file column I is full of formulas. From post 1 I had assumed that the section from column H on would be empty (apart perhaps from headings).
   
   
6. Your sample file has what appears to be extraneous data (error values) in rows 2995:3000. Should they be there?

I'm sure that if you can resolve the above conflicts and decide just what columns and format you have to start with and what results you want and where, the code can be modified to get the results you want.

In relation to point 2 above, I would suggest changing the column B formula to the following to avoid the error values
=IF(A2="","",LEFT(A2,MIN(--(FIND(CHAR(ROW($65:$90)),A2&CHAR(ROW($65:$90)),3)))-1))

.. or based on the samples I've seen, would this do the same job, just stripping off the last character?
=IF(A2="","",LEFT(A2,LEN(A2)-1))