Fill unique header data in each data section as row data

L

lindstroem

New Member
Joined
Mar 2, 2015
Messages
39
Office Version
  1. 2016
Hello
I have a large datasheet (2000 rows) coming in each month with a alot of cost data which I need to put in a Pivot Table (or prepare for PowerBI).

The problem however is that the datarows do not include which organisation the cost belongs to which is needed. So in the example below, the organisation ID is a header for each segment (starts at A3 below) which i need to be copied in to the column D for all rows that contain a date-intervall in column A. I've tried to play around with COUNTIF(A:A="Organisations*") to start to get the total value but I am lost on how to search it out and dynamically change it for each section where a new Org-ID is used (Also I would prefer only to get the org value and name and not the "Organisations ID:" on each row. Any suggestions are most welcome.

A second question is if there is a good way to delete all rows that does not contain the dates after the first formula has completed (the formula above would then need to be copied/pasted as value for it to work, so assume that would require a macro)

Any thoughts on a elegant Excel-formula or macro would be much appriciated!



Organisations ID: 2764 - MittSverige Vatten & Avfall
Kostnadsställe:2764NEW ROW WHERE I NEED FORMULA
FakturaperiodInv. NamnOrganisations ID
20210401-20210430KvisslebyVerket-12764 - MittSverige Vatten & Avfall
20210401-20210430MatforsVerket-12764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-12764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-102764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-112764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-122764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-132764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-142764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-152764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-162764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-22764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-32764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-42764 - MittSverige Vatten & Avfall
20210401-20210430Tivoliverket-Berget-52764 - MittSverige Vatten & Avfall
Organisations ID: 2824 - MSVA - EXTERN
Kostnadsställe:2824
FakturaperiodInv. Namn
20210401-20210430iPad (VPN) Robert Björkman2824 - MSVA - EXTERN
20210401-20210430IN222012824 - MSVA - EXTERN
20210401-20210430IN222022824 - MSVA - EXTERN
20210401-20210430IN222032824 - MSVA - EXTERN
20210401-20210430IN222112824 - MSVA - EXTERN
20210401-20210430IN117752824 - MSVA - EXTERN
20210401-20210430IN221672824 - MSVA - EXTERN
20210401-20210430IN221792824 - MSVA - EXTERN
20210401-20210430IN221802824 - MSVA - EXTERN
20210401-20210430IN240662824 - MSVA - EXTERN
20210401-20210430AutoCAD MEP 2015 (MSVA)2824 - MSVA - EXTERN
20210401-20210430Cad-q XL-V2824 - MSVA - EXTERN
20210301-2021033107246479612824 - MSVA - EXTERN
20210401-20210430IN242072824 - MSVA - EXTERN
20210401-20210430Distansåtkomst iPad (VPN)2824 - MSVA - EXTERN
20210401-20210430IN164312824 - MSVA - EXTERN
Organisations ID: 5133 - MSVA - Ledningsnät
Kostnadsställe:5133
FakturaperiodInv. Namn5133 - MSVA - Ledningsnät
20210401-20210430IN231975133 - MSVA - Ledningsnät
20210401-20210430IN233635133 - MSVA - Ledningsnät
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Its going to come down to what you are comfortable with.

The old fashioned way is below. To refresh you would delete the data (content not rows or columns) in Columns A-C, copy in the new data, make sure your formulas go all the way to the bottom. You can then filter on Yes in the last column and copy the visible rows as values somewhere else.
If you convert it to a table you would not even need to extend the formulas

Power Query will do what you want. It would need you to change the data source to an Excel table so that it auto expands. You would then just need to refresh the query.

VBA (a macro) would also be an option.

PS: it would also help to know how you are getting the data into Excel. If you opening a file and copying it in, the Macro or Power Query would use that as input and cut out the copying it into Excel step.

Old Fashioned way example

20210507 Repeat Header Info on Each Row.xlsx
ABCDEF
1OrganisationData Line
2OrganisationNo
3Organisations ID: 2764 - MittSverige Vatten & Avfall2764 - MittSverige Vatten & AvfallNo
4Kostnadsställe:27642764 - MittSverige Vatten & AvfallNo
5FakturaperiodInv. Namn2764 - MittSverige Vatten & AvfallNo
620210401-20210430KvisslebyVerket-12764 - MittSverige Vatten & AvfallYes
720210401-20210430MatforsVerket-12764 - MittSverige Vatten & AvfallYes
820210401-20210430Tivoliverket-Berget-12764 - MittSverige Vatten & AvfallYes
920210401-20210430Tivoliverket-Berget-102764 - MittSverige Vatten & AvfallYes
1020210401-20210430Tivoliverket-Berget-112764 - MittSverige Vatten & AvfallYes
1120210401-20210430Tivoliverket-Berget-122764 - MittSverige Vatten & AvfallYes
1220210401-20210430Tivoliverket-Berget-132764 - MittSverige Vatten & AvfallYes
1320210401-20210430Tivoliverket-Berget-142764 - MittSverige Vatten & AvfallYes
1420210401-20210430Tivoliverket-Berget-152764 - MittSverige Vatten & AvfallYes
1520210401-20210430Tivoliverket-Berget-162764 - MittSverige Vatten & AvfallYes
1620210401-20210430Tivoliverket-Berget-22764 - MittSverige Vatten & AvfallYes
1720210401-20210430Tivoliverket-Berget-32764 - MittSverige Vatten & AvfallYes
1820210401-20210430Tivoliverket-Berget-42764 - MittSverige Vatten & AvfallYes
1920210401-20210430Tivoliverket-Berget-52764 - MittSverige Vatten & AvfallYes
20Organisations ID: 2824 - MSVA - EXTERN2824 - MSVA - EXTERNNo
21Kostnadsställe:28242824 - MSVA - EXTERNNo
22FakturaperiodInv. Namn2824 - MSVA - EXTERNNo
2320210401-20210430iPad (VPN) Robert Björkman2824 - MSVA - EXTERNYes
2420210401-20210430IN222012824 - MSVA - EXTERNYes
2520210401-20210430IN222022824 - MSVA - EXTERNYes
2620210401-20210430IN222032824 - MSVA - EXTERNYes
2720210401-20210430IN222112824 - MSVA - EXTERNYes
2820210401-20210430IN117752824 - MSVA - EXTERNYes
2920210401-20210430IN221672824 - MSVA - EXTERNYes
3020210401-20210430IN221792824 - MSVA - EXTERNYes
3120210401-20210430IN221802824 - MSVA - EXTERNYes
3220210401-20210430IN240662824 - MSVA - EXTERNYes
3320210401-20210430AutoCAD MEP 2015 (MSVA)2824 - MSVA - EXTERNYes
3420210401-20210430Cad-q XL-V2824 - MSVA - EXTERNYes
3520210301-202103317246479612824 - MSVA - EXTERNYes
3620210401-20210430IN242072824 - MSVA - EXTERNYes
3720210401-20210430Distansåtkomst iPad (VPN)2824 - MSVA - EXTERNYes
3820210401-20210430IN164312824 - MSVA - EXTERNYes
39Organisations ID: 5133 - MSVA - Ledningsnät5133 - MSVA - LedningsnätNo
40Kostnadsställe:51335133 - MSVA - LedningsnätNo
41FakturaperiodInv. Namn5133 - MSVA - LedningsnätNo
4220210401-20210430IN231975133 - MSVA - LedningsnätYes
4320210401-20210430IN233635133 - MSVA - LedningsnätYes
44
Data Orig (2)
Cell Formulas
RangeFormula
D2:D43D2=IF(LEFT(A2,18)="Organisations ID: ",RIGHT(A2,LEN(A2)-18),D1)
E2:E43E2=IFERROR(IF(FIND("-",A2)=9,"Yes","No"),"No")
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA to Split Data From an Existing Table into Another Single Worksheet with Header for Each Section
Replies
1
Views
508
John_w
J
Saher Naji
  • Question
Auto-Fill values (2)
Replies
1
Views
583
alansidman
A
ExcelAtEverything
  • Solved
Power Query - Multiply column contents if row "Date" is before specified date
Replies
1
Views
4K
ExcelAtEverything
ExcelAtEverything
R
  • Question
Provide Count based upon criteria in Data along with unique row and header data
Replies
2
Views
457
rugbyhubby
R
I
  • Question
Re: Splitting out excel data to multiple worksheets based on one row's value
Replies
1
Views
548
tlowry
T

Forum statistics

Threads
1,221,443
Messages
6,159,907
Members
451,601
Latest member
terrynelson55

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top