Copy “Comments” using VBA, with a twist.

[julhs]

Have a large “Input Section” (16 Cols & 500-700 Rows) that has comments scattered throughout it, currently my “Analysis Section” only contains values taken from the “Input Section” using {=IF(SUMPRODUCT,ROWS,INDEX,SMALL………)}

My understanding is that I can’t use a formula to get the “Comments” from the “Input Section” into the corresponding cell in the “Analysis Section”.

What I want to do is get the Values & Comments into the Analysis Section, presently I have to copy/paste "Comments" individually.

Is there a way to do this retrospect fully with VBA from a Command button?
P.S "Comments" dont seem to be appearing on XL2BB, cells with "Fill colour" are ones with Comments

Accounts 2016 - 2019 Final Currant.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		CASH BOOK
3		DEBITS
4		Date	Invoice #	Payment Method List	Payment Details List	Bank & Cash	Rent	Power	Motoring Expenses	Stock & Materials	Office
5
6		Jul-05	gs0370	Direct Debit	Rent	250.00	250.00						INPUT SECTION All details etc on left are Enterd via a USERFORM, including the comments. It finds the next empty Row & pastes in Userform values, then inserts a new blank row and formats it
7		Jul-10	gs0375	Direct Debit	EDF	35.00		35.00
8		Jul-15	gs0380	Debit Card	Micks Garage	50.00			50.00
9		Jul-20	gs0385	Debit Card	Halfords	20.00			20.00
10		Jan-21	gs0390	Debit Card	Southern Building Supplies	31.00				31.00
11		Jul-22	gs0415	Debit Card	Amazon	42.00					42.00
12		Jul-24	gs0433	PayPal	Direct Stationary Supplies	10.00					10.00
13		Aug-05	gs0450	Direct Debit	Rent	275.00	275.00
14		Aug-10	gs0460	Debit Card	Wessex Steel	40.00				40.00
15		Aug-10	gs0462	Direct Debit	EDF	50.00		50.00
16
17					Totals	803.00	525.00	85.00	70.00	71.00	52.00
18
19
20				ANALYSIS SECTION			Rent	EDF	Micks Garage	Halfords	Southern Building Supplies	Wessex Steel	Direct Stationary Supplies	Amazon
21				This section lists all the entries for individual supplier using SUMPRODUCT,ROWS,INDEX,SMALL. It does get bumped down as rows are added above			250.00	35.00	50.00	20.00	31.00	40.00	10.00	42.00
22							275.00	50.00
23
24
25				This is what I would like the desired outcome to be			Rent	EDF	Micks Garage	Halfords	Southern Building Supplies	Wessex Steel	Direct Stationary Supplies	Amazon
26							250.00	35.00	50.00	20.00	31.00	40.00	10.00	42.00
27							275.00	50.00
Testing (2)

Cell Formulas
Range	Formula
F17:K17	F17	=SUM(F6:F15)
G21:N22	G21	=IF(SUMPRODUCT(--($E$5:$E$15=G$20))>=ROWS(G$21:G21),INDEX($F$5:$F$15,SMALL(IF($E$5:$E$15=G$20,ROW($E$5:$E$15)-ROW($E$5)+1),ROWS(G$21:G21))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Micron]

Did you consider copy (from Micks's to EDF, I guess) then paste special>All>transpose?
Your target columns would have to be in the same order as your copied rows of course. I tried that and the comments were pasted. A lot easier than code?

 

[julhs]

Thanks for reply Micron.
Entries in “Input section” will be in utterly random pattern, the “Analysis Section” has fixed Columns, but Row # will change due to being bumped down by the “Input Section”

 

[Micron]

Does row# matter when copying/pasting? As for the randomness I presume you mean the order of things like the cells from Mick's to Rent? When you copy/paste, those cells become headers. So input

with output as

Sorry if I'm not following why that can't work. At the very least, I suppose you could go through the motions while recording a macro and get some clue as to the code you'd need if that's the route you need to take. BTW, not sure what those little red triangles are in your analysis section pic. They don't look like they represent errors, notes or threaded comments. The purple ones I show are threaded comments, which do paste into the new cells.

 

[julhs]

For some reason XL2BB didn’t upload/show ANY Comments on the Mini Sheet that are on my ACTUAL sheet, had to resort to “Fill Colour” luminous green to indicate which cells had Comments. Ordinary “Comments” are red triangle in cells top right corner, SOME of the cells you fixed with purple triangle DO have “Comments”, but not all.

Just so we on the same lines:

This is an Accounts spreadsheet
Entries in “Input Section” are in random order (so the date/order is based on the date the Debit is made, irrespective of supplier name order)

I’m not using Copy/Paste FROM “Input section” to “Analysis Section”, the “Analysis Section” is using a formula {=IF(SUMPRODUCT,ROWS,INDEX,SMALL………)}
to pull the figures from the “Input Section” and list them under the Individual Suppliers, ie Rent,EDF,Micks Garage etc,

The “Analysis Section” has fixed headers.

What currently happens is, IF ANY entry in Col E matches the cell value in eg G20 (Rent), it pulls value from F20 and THAT value is placed it in the next blank cell under “Rent”, BUT WITHOUT the “Comment.”

What I’m trying do is pull FROM the “Input Section” the “Value” and the “Comment” from ie F13(Rent) into the next blank row under ie Rent

Everything I have posted works fine just to pull values from “Input Section” into “Analysis Section”, but I want to pull “Comments” at the time as well,

If formula option is off the table then maybe use VBA to get the “Comments” from Col F to relavant cells in G26:N27

I do pray this has made things clearer as opposed to more confusion!!!

 

[Micron]

Sorry if I seemed to be pushing the transpose thing too much. It's just that without knowing how it all works it just seemed far simpler. I bet it can be done with vba. I have to sign off for a bit, but in the meantime, if you search posts I've made in the last 3 to 5 days, there have been at least two threads where I and others were dealing with notes (formerly known as comments) and "comments" (now called ThreadedComments I believe). Perhaps there is something in those that would get you started on code.
Here's one: Copying and Pasting CommentThreaded

The other also started by same OP as that one.

 

[julhs]

Got side tracked and Message edit timed out.
Rephraseing/edit of above.

For some reason XL2BB didn’t upload/show ANY Comments on the Mini Sheet that are on my ACTUAL sheet, had to resort to “Fill Colour” luminous green to indicate which cells had Comments. Ordinary “Comments” are red triangle in cells top right corner; SOME of the cells you fixed with purple triangle DO have “Comments”, but not all.

Just so we on the same lines:
This is an Accounts spreadsheet
Entries in “Input Section” are in random order (so the date is when Debit is made, irrespective of supplier name)

I’m not using Copy/Paste FROM “Input section” to “Analysis Section”, the “Analysis Section” is using a formula {=IF(SUMPRODUCT,ROWS,INDEX,SMALL………)}
to pull the figures from the “Input Section” and list them under the Individual Suppliers, ie Rent,EDF,Micks Garage etc,

The “Analysis Section” has fixed headers.

What currently happens is, IF ANY entry in Col E matches the cell value in eg G20 (Rent), it pulls the next value from Col F and THAT value is placed it in the next blank cell under “Rent” (G20), BUT WITHOUT the “Comment.”

What I’m trying do is FROM the “Input Section” pull the “Value” and the “Comment” from ie, F??(Rent) into the next blank row under ie Rent (G20)

Everything I have posted works fine just to pull values from “Input Section” into “Analysis Section”, but I want to pull “Comments” at the time as well,
if formula option is off the table then maybe use VBA to get the “Comments” from “ Input Section” Col F to relevant cells in G26:N27 retrospect fully using separate VBA code

I do pray this has made things clearer as opposed to add more confusion, but I have my doubts!!!

 

[julhs]

Can I stress again XI2BB didn’t show any comments that are in F5:K15 or G26:N27 so used “Fill colour” – luminous green to indicate the cells with “Comments”.
IGNORE the fact there are “Comments” in G6:K15 as they are only replicates of ones in F6:F15

The “Red/Orange” triangles in Analysis section (G21:N22) are {Array formulas} that look for entries in E5:E15 that match the names in G20:N20. If there is a match it then pulls the corresponding value from F5:F15 and puts/adds that value to the list under the “Matched Name” in the “Analysis Section”

The number of rows in “Input Section” continually increases, because the “Userform” enters its data into the first BLANK row of the “Input Section” (ie Row16) and THEN ALSO ADDS a new-blank-formatted row, so effectively bumping everything in E17:N27 down by one row.
(“Input Section” could end up having/being 500-700 rows, “Analysis Section” will then effectively be bumped down by same amount)

Transpose isn’t an option because names in F5:F??? “Input Section” will be in random order while the “Analysis Section” has fixed headings (G20:N20)

 

[julhs]

Hi Micron,
I have failed miserably to adapt the code on the link you posted regarding copying “Comments/Notes”!!
But also been researching alternative methods to achieve what I was planning.

Is there any chance you could have a look at that code and adapt it for me to fit with a slightly different criteria to my original post??

My Minimum thinking right now is to simply copy ALL the Comments/Notes from Col F into Col Q.
Next best thing is to; retrieve values from Col E into Col P and also/then retrieve from Col F the ”Comments & values” into Col Q.

That way I may be able to filter/vLookup those Columns for specific values?

Appreciate any guidence you can give!!
Julhs

 

[Micron]

I'll see what I can do with the 2nd option, which seems like it would be your preferred result. Not sure because it seems more robust, but to me, "next best thing" implies that which is secondary, inferior, less desirable, etc.