SOLVED

How do I find Origin of an excel sheet?

%3CLINGO-SUB%20id%3D%22lingo-sub-2463676%22%20slang%3D%22en-US%22%3EHow%20do%20I%20find%20Origin%20of%20an%20excel%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2463676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20am%20trying%20to%20answer%20this%20question%26gt%3BWhat%20is%20the%20most%20common%20origin%3F%20How%20many%20cases%20have%20that%20origin%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2463676%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464057%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20find%20Origin%20of%20an%20excel%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1082555%22%20target%3D%22_blank%22%3E%40aimes_12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22D6j0vc%22%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22wDYxhc%22%3E%3CDIV%20class%3D%22di3YZe%22%3E%3CDIV%20class%3D%22co8aDb%20XcVN5d%22%3E%3CSTRONG%3EFind%20out%20which%20data%20sources%20are%20used%20in%20a%20workbook%20data%20model%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%20class%3D%22RqBzHd%22%3E%3COL%20class%3D%22X5LH0c%22%3E%3CLI%3EIn%20%3CSTRONG%3EExcel%3C%2FSTRONG%3E%2C%20click%20Power%20Pivot%20%26gt%3B%20Manage%20%3CSTRONG%3Eto%3C%2FSTRONG%3E%20open%20the%20Power%20Pivot%20window.%3C%2FLI%3E%3CLI%3EView%20the%20tabs%20in%20the%20Power%20Pivot%20window.%20Each%20tab%20contains%20a%20%3CSTRONG%3Etable%3C%2FSTRONG%3E%20in%20your%20model.%20...%3C%2FLI%3E%3CLI%3E%3CSTRONG%3ETo%3C%2FSTRONG%3E%20view%20the%20%3CSTRONG%3Eorigin%3C%2FSTRONG%3E%20of%20the%20%3CSTRONG%3Etable%3C%2FSTRONG%3E%2C%20click%20%3CSTRONG%3ETable%3C%2FSTRONG%3E%20Properties.%3C%2FLI%3E%3C%2FOL%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Ffind-out-which-data-sources-are-used-in-a-workbook-data-model-44ef9af5-fffd-45a2-8f7c-caa04a2f774f%3Fui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EFind%20out%20which%20data%20sources%20are%20used%20in%20a%20workbook%20data%20model%3C%2FA%3E%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3EIf%20you're%20working%20with%20data%20that's%20been%20added%20to%20an%20Excel%20data%20model%2C%20then%20sometimes%2C%20you%20may%20lose%20track%20of%20which%20tables%20and%20data%20sources%20were%20added%20to%20the%20data%20model.%3C%2FP%3E%3CDIV%20class%3D%22ocpAlert%22%3E%3CP%20class%3D%22ocpAlertSection%22%3E%3CSTRONG%3ENote%3A%3C%2FSTRONG%3E%26nbsp%3BMake%20sure%20you%20have%20enabled%20the%20Power%20Pivot%20add-in.%20For%20more%20information%2C%20see%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fstart-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EStart%20the%20Power%20Pivot%20add-in%20for%20Excel%3C%2FA%3E.%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464123%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20find%20Origin%20of%20an%20excel%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1082555%22%20target%3D%22_blank%22%3E%40aimes_12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20would%20help%20if%20you%20provided%20more%20detailed%20information.%20If%20you%20want%20to%20know%20the%20most%20frequently%20occurring%20value%20in%20a%20series%20of%20text%20values%2C%20you%20can%20use%20something%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0523.png%22%20style%3D%22width%3A%20579px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F290014iB6717276122BCDAF%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0523.png%22%20alt%3D%22S0523.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%20that%20returns%20the%20most%20frequently%20occurring%20value%20is%3C%2FP%3E%0A%3CP%3E%3DINDEX(A2%3AA20%2CMODE(MATCH(A2%3AA20%2CA2%3AA20%2C0)))%3C%2FP%3E%0A%3CP%3EAnd%20the%20frequency%20of%20that%20value%20is%3C%2FP%3E%0A%3CP%3E%3DCOUNTIF(A2%3AA20%2CC2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464124%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20find%20Origin%20of%20an%20excel%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464124%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40NikolinoDE%3C%2FA%3E%20I%20figured%20that%20one%20out.%20Thank%20you%20for%20your%20help.%3CBR%20%2F%3ENow%20I%20am%20trying%20to%20convert%20a%20column%20with%20hours%20to%20days%2C%20do%20you%20have%20a%20formula%20for%20that%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464156%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20find%20Origin%20of%20an%20excel%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1082555%22%20target%3D%22_blank%22%3E%40aimes_12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EI%20don't%20know%20exactly%20what%20you%20want%20%2F%20need%20and%20what%20you%20want%20to%20calculate%2C%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3Ebut%20I%20am%20still%20sending%20you%20this%20file%20with%20the%20time%20calculation.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EIf%20that%20is%20not%20exactly%20what%20you%20want%2C%20please%20explain%20again%20in%20detail%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E(on%20the%20basis%20of%20a%20file%2C%20without%20sensitive%20data%2C%20would%20be%20best)%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3Eand%20I%20will%20try%20to%20help%20you%20as%20far%20as%20I%20can.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464219%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20find%20Origin%20of%20an%20excel%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464219%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40NikolinoDE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esry%20now%20i%20see%20your%20question%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20is%20a%20examble%20hours%20to%20days%20with%20formulas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464228%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20find%20Origin%20of%20an%20excel%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464228%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20what%20is%20the%20formula%20to%20convert%20an%20entire%20column%20that%20is%20in%20hours%20to%20days%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464267%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20find%20Origin%20of%20an%20excel%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464267%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1082555%22%20target%3D%22_blank%22%3E%40aimes_12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20NikolinoDE's%20reply.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am trying to answer this question>What is the most common origin? How many cases have that origin?

10 Replies

@aimes_12 

 
Find out which data sources are used in a workbook data model
  1. In Excel, click Power Pivot > Manage to open the Power Pivot window.
  2. View the tabs in the Power Pivot window. Each tab contains a table in your model. ...
  3. To view the origin of the table, click Table Properties.

Find out which data sources are used in a workbook data model

If you're working with data that's been added to an Excel data model, then sometimes, you may lose track of which tables and data sources were added to the data model.

Note: Make sure you have enabled the Power Pivot add-in. For more information, see Start the Power Pivot add-in for Excel.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

@aimes_12 

It would help if you provided more detailed information. If you want to know the most frequently occurring value in a series of text values, you can use something like this:

S0523.png

The formula that returns the most frequently occurring value is

=INDEX(A2:A20,MODE(MATCH(A2:A20,A2:A20,0)))

And the frequency of that value is

=COUNTIF(A2:A20,C2)

 

 

@NikolinoDE I figured that one out. Thank you for your help.
Now I am trying to convert a column with hours to days, do you have a formula for that?

@aimes_12 

I don't know exactly what you want / need and what you want to calculate,

but I am still sending you this file with the time calculation.

If that is not exactly what you want, please explain again in detail

(on the basis of a file, without sensitive data, would be best)

and I will try to help you as far as I can.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

@NikolinoDE 

 

sry now i see your question

 

here is a examble hours to days with formulas

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

@Hans Vogelaar what is the formula to convert an entire column that is in hours to days?

@aimes_12 

See NikolinoDE's reply.

I apologize, I am very novice to Excel. If I have Column b1:b166, how do I add that to the formula's below?
=INT(C2/24*60) & " Days " & MOD(C2/24*60,1)*24 & " hours"
or
=INT(C2)&" days "&TEXT(C2-INT(C2),"h:mm")&" hrs. "
best response confirmed by allyreckerman (Microsoft)
Solution

@aimes_12 

If B1 contains a umber of hours, for example 36, you can use

 

=B1/24

 

to return 1.5 as the number of days

@@Hans Vogelaar and @@NikolinoDE Thank you both so much! Have great day!