Formula to alamgamate and edit?

[LMills77]

Hi all,

I’ve been given a project at work to try and tidy up a spreadsheet. I have several questions but for the time being, I’m dealing with the first problem I’m come across.

When a payment arrives, the system notes it correctly but does not add the comment into one line; it breaks it up into 3 lines and when an Excel report is generated, it shows on 3 lines. Attached is how they come out and how I want them to come out. Can they be cut and pasted and amalgamated into the first row?

If this is possible, I have the issue of deleting those blank rows once the data is removed from them; is this possible? Maybe a keyboard shortcut or Macros?

I may keep adding questions to this thread as I move along.

Thanks for your help,

Lee.

[JohnTopley]

Do the 3 lines have a consistent format, in particular line 2 with the numeric data?

And what volumes (number of invoices) need converting ?

[NickyC]

Hi
if you just want a formula this should do it:
=TRIM(E5)&" "&TRIM(SUBSTITUTE(E6,",",", "))&" " &E7

The macro get a bit complicated because, as John indicates, it will depend on how the data are laid out. Assuming there are always three rows of text to combine in column E and the date value in column A is always followed by 2 blank rows then this might work:

Please Login or Register  to view this content.

assuming there are always three rows of text in column E to combine, this will

[LMills77]

Hi Nicky,

That formula is great - thanks.

However, may I throw another issue in? Regarding the 6642, 86643, 86644, - on some lines, there are only 3 sets of numbers and in others there could be 8 sets of numbers. It varies on different lines. Can the formula be amended to reflect this change?

Also, I'm looking for a second formula to search for an invoice number in the 'Allocated to...' column. In the spreadsheet, I've added a second tab called '2nd Formula.' Is there a way to highlight / show the invoices being paid?

Thanks for your help,

Lee.

[LMills77]

Hello John,

Yes, the 3 lines have consistent formatting.

Regarding the number of invoices, this varies with each entry. Some can have 2 invoice numbers, while others can have 10 or more. The reason I asked if they could be spaced was if a search formula was used in this column, would it pick up the invoice number without spaces?

Thanks for your help,

Lee.

[JohnTopley]

The code supplied handles multiple invoices

[JohnTopley]

How are you going to select the "Invoice" line and hence invoices you want highlighted?

[LMills77]

How are you going to select the "Invoice" line and hence invoices you want highlighted?

Hello John,

I was thinking something like a cell being filled with 'Paid' or something similar? Would that work?

Lee.

[JohnTopley]

If it is selected from the sheet where the data is combined, then there is a possibility to use VBA to highlight the invoices.

At some point you will want to remove the highlighting ??? otherwise you could end up with all invoices highlighted.

Or have I misunderstood your requirement?

[LMills77]

No, I think that is correct.

To add some context - the workbook might list 1,000 invoices, of which we have payments on the account for 500 of those invoices. Instead of manually locating and highlighting them, I thought there could be a way of highlighting those listed in payments postings. There will come a time when the highlighting will be removed but I haven't thought that far ahead yet.

Thanks again,

Lee.

[JohnTopley]

Thinking aloud: if there are number of "invoice" sets, I suggest it each set is coloured differently.

Can you add some more sample data to the file in post #4, including the "trigger" (paid ?).

I'll try and look at it over the weekend.