Ugly report format to per row dataset

[butch3]

I have an interesting challenge that I need help with the macro code to reorganize the output.

Essentially, I need a report to output items of data on one row but the output currently is on multiple rows.

Unfortunately, I can't download the XL2BB but I am posting input and output pictures and the text for copy and paste. Also, the cells where the data is located in the input image are just an example. The actual cells can potentially be located anywhere in the document. The end of the document is where there are 10 empty rows?

The challenge is that some of the headers have text on 2 separate lines (See A44 and A45 of the input picture for an example). Also, the other challenge is that each row of the output will have to have the result of whatever is after the "CS:" until the next one appears in the report input (see A43 and A58 input picture for an example).

Except for the colors that I added, the input report looks the way it does because I 'select all' cells and 'clear format' to remove any merge of the document.

I really only need the code for the first couple of columns and I can figure out the rest but if you want to provide all the code, that's fine as well.


Input Image:

Output Image:
1. Output should be placed in new tab
2. color coding is not necessary.
3. columns that are the width of the longest text is preferred - no more though
4. dates should be converted to date format and where amounts are (rather than numbers) should be in dollar format

Text:

CS: B
Plan		Component		C Number	Project	Estimated	Type of	S	Planned	Project	Project Officer /
No.					Title	Amount	Action	Released Date	Date	Manager	Ph. #
220625		GHIC		N/A	New Project	12345	New		44813	Me	Another Name
R		Num		Type		RFC Due Date	RFC Rcvd Date	R Rcvd. Date			C Amount
123		---				44751
Status/Remarks:	Exercise
Total R for 220625: 0
CS: A
Plan		Component		C Number	Project	Estimated	Type of	S	Planned	Project	Project Officer /
No.					Title	Amount	Action	Released Date	Date	Manager	Ph. #
220625		GHIC		N/A	New Project	12345	New		44813	Me	Another Name
R		Num		Type		RFC Due Date	RFC Rcvd Date	R Rcvd. Date			C Amount
324		---				44751
Status/Remarks:	Exercise

CS

Plan No.

Component

C Number

Project Title

Estimated Amount

Type of Action

S Released Date

Planned Date

Project Manager

Project Officer / Ph. #

R

Num

Type

RFC Due Date

RFC Rcvd Date

R Rcvd. Date

C Amount

Status/Remarks

B

220625

GHIC

N/A

New Project

12345

New

44813

Me

Another Name

123

---

44751

Exercise

 

[earthworm]

Very easy . I can give formula approach as a template that will organize data so that you can paste it into other maintained workbook . If interested kindly share Excel file

 

[butch3]

Very easy . I can give formula approach as a template that will organize data so that you can paste it into other maintained workbook . If interested kindly share Excel file

With a formula approach, I will have to download the weekly document and then add the formula cells to a new tab - which is a lot more manual than I wanted. I would rather use a VBA approach.

Unfortunately, I can't upload the file but I can upload a slightly similar file. How do I upload the file?

 

[earthworm]

With a formula approach, I will have to download the weekly document and then add the formula cells to a new tab - which is a lot more manual than I wanted. I would rather use a VBA approach.

Unfortunately, I can't upload the file but I can upload a slightly similar file. How do I upload the file?

No . You only need to dump.the data the rest will be visible in another clean sheet

 

[butch3]

That works. How do I get the sample file to you.

 

[Fluff]

You will need to upload the file to a share site, mark for sharing & then post the link you are given here.