Hi Rod, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.
Can you send me a sample or dummy of your Excel file so that I can directly check the structure of your data? You can create a copy of the file, remove sensitive info, and attach it here using the Add Files button.
Please let me know by replying to me here so that I can help you further.
Thank you. Please take your time
Thank you, ***** ***** me a moment to download and check your file.
Please tell me if this approach will work:
Will that work?
OK, that will be alright then. But what I was saying was that in order to use formulas to automate this, we need to utilize other columns while the formulas work on the original A and B columns.
Thank you. Please give me a moment to create the formulas for you so that you can test on your actual files.
I am still working on the required formula... Please give me a moment
There are a lot of data so my array calculation is taking some time to show up. Thank you for your patience.
I am also trying a Macro approach. I need to see which is faster
I believe I miss something. Are you saying that the final summary table will only show those rows with values?
In the sample, you have almost 11,000 rows but few hundred rows with values. So you want to only display the rows with values and ignore the others, right?
Thank you for the confirmation. Since you have a lot of data, extract the unique data will take a lot of time with array formulas and could cause Excel to freeze. What about the option to just manually extract the rows with data manually deleting those with blank rows, then work on the smaller file?
Ok, this sample file is smaller now, give me a moment to apply the formulas
Here is the file:
The file contains a summary table I created with formula. However, since there are no duplicates, the table is exactly the same.
Can you send me a sample or old file with few duplicates to test the formula template I created?
You can use this as a template. With your other files, you manually extract the rows with data, copy them, and then paste them in columns A and B starting row 2 since row 1 is the header. Then the summary table will automatically populate the data based on the data you pasted.
However, that template I just sent you only has 117 rows in the formula. You just need to drag the formula down to the last row you pasted.
Here is a modified version of the file, I already tested it by copying and pasting the tail end part of the data to produce duplicates, and the formulas just worked fine.
Also, I added IFERROR function to show blank cells when errors occur.
Here is the link:
And oh, YES, you are correct. You need to copy and paste 3 columns A, B and C!
You're welcome, Rod. Please take your time.
If you need further assistance with the template file I created for you, just let me know so that I can assist you further. Please bookmark or add our chat page to Favorites so that you can get back to me here in this chat easily.
I hope that helped.Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!
If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.
If you paste new data in columns A, B and C, the summary table will automatically populate with the correct data. Please check and verify that.
But in the last template I gave you, it works only up to row 124. If the pasted data exceeds row 124, please click on the last cell of the table and drag to the last row of the pasted data to cover the actual length of your data.
If you paste only few data, it will be no problem, the cells BELOW will show blank.
The formula is working, but there are few things you need to adjust when the data you paste is greater that of the existing table. You will see this easily because the table has colors. So if the data in ABC is shown past the table, then you need to do some adjustments.
Here are the adjustments you need to do:
Doing all these 3 adjustments will fix the problem, Remember, if you paste fewer data, there will be no need for adjustments, only when pasting larger data past the summary table.
Here is the final file: